Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    10

    Unanswered: Variable 'name' of XMLELEMENT

    since I'm new with postgresql and XMLELEMENT function has a bit different syntax than oracle one I have a question:

    How to pass variable for name argument of XMLELEMENT function.

    Reason: i want to create function which will build XML using node name passed as argument


    Code:
    CREATE OR REPLACE FUNCTION somefunction(ticketnumber INT, nodename varchar(50))
      RETURNS xml AS
    $BODY$
    DECLARE
    xml_elements XML;
    BEGIN
    
    	-- some code
    
    
    	xml_node = xmlelement(name nodename, null, xml_elements );
    
    	return xml_node;
    END;
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE
      COST 100;
    of course this code will not work because syntax error while passing nodename variable to the xmlelement.

    Best regards

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    The syntax isn't really different from Oracles save the required 'name'. You'll have the same problem in Oracle though.

    If you want to do this, you are going to have to something like:
    Code:
    FUNCTION named_node(name VARCHAR, contents text)
    RETURN XML IS
    $$
      SELECT XML('<' || $1 || '>' || $2 || '</' || $1 || '>');
    $$ LANGUAGE 'sql';

  3. #3
    Join Date
    Apr 2010
    Posts
    10
    I's sure I saw an oracle example with
    XMLELEMENT (name 'somename', ...)
    which makes possible to use variable instead of the string.
    Probably I'm wrong. But never mind.

    Thanx for idea. I thought about it before. But wasn't sure I am missing some syntax or it is really pg issue.

    However it is really interesting that pg devs have left such construct in the language, making use of variables impossible

    Best regards
    Last edited by MaXyM; 04-29-10 at 13:49.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    I'm sure they were following the behavior outlined in the SQL/XML spec.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •