Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

XMLELEMENT function [String]

Produces an XML element within a query.

XMLELEMENT( { NAME element-name-expression | string-expression }
[, XMLATTRIBUTES ( attribute-value-expression
[ AS attribute-name ],... )
[, element-content-expression,... ] )

element-name-expression    An identifier. For each row, an XML element with the same name as the identifier is generated.

attribute-value-expression    An attribute of the element. This optional argument allows you to specify an attribute value for the generated element. This argument specifies the attribute name and content. If the attribute-value-expression is a column name, then the attribute name defaults to the column name. You can change the attribute name by specifying the attribute-name argument.

element-content-expression    The content of the element. This can be any string expression. You can specify an unlimited number of element-content-expression arguments and they are concatenated together. For example, the following SELECT statement returns the value <x>abcdef</x>:

SELECT XMLELEMENT( NAME x, 'abc', 'def' );

NULL element values and NULL attribute values are omitted from the result. The letter case for both element and attribute names is taken from the query.

Element content is always escaped unless the data type is XML. Invalid element and attribute names are also quoted. For example, consider the following statement:

SELECT XMLELEMENT('H1', f_get_page_heading() );

If the function f_get_page_heading is defined as RETURNS LONG VARCHAR or RETURNS VARCHAR(1000), then the result is HTML encoded:

CREATE FUNCTION f_get_page_heading() RETURNS LONG VARCHAR
   BEGIN
      RETURN ('<B>My Heading</B>');
   END;

The above SELECT statement returns the following:

<H1>&lt;B&gt;My Heading&lt;/B&gt;</H1>

If the function is declared as RETURNS XML, then the above SELECT statement returns the following:

<H1><B>My Heading</B></H1>

For more information about quoting and the XMLELEMENT function, see Invalid names and SQL/XML.

XMLELEMENT functions can be nested to create a hierarchy. If you want to return different elements at the same level of the document hierarchy, use the XMLFOREST function.

For more information, see XMLFOREST function [String].

Data in BINARY, LONG BINARY, IMAGE, and VARBINARY columns is automatically returned in base64-encoded format when you execute a query that contains the XMLELEMENT function.

• Using the XMLELEMENT function
• XMLFOREST function [String]
• String functions

• Part of the SQL/XML draft standard.

• Omitting the NAME keyword and using a string expression as the first argument is a vendor extension.

The following example produces an <item_name> element for each product in the result set, where the product name is the content of the element.

SELECT ID, XMLELEMENT( NAME item_name, p.Name )
FROM Products p
WHERE ID > 400;

The following example returns <A HREF="http://www.ianywhere.com/" TARGET="_top">iAnywhere website</A>:

SELECT XMLELEMENT( 
   'A',
   XMLATTRIBUTES( 'http://www.ianywhere.com/' 
      AS "HREF", '_top' AS "TARGET"),
   'iAnywhere website'
);

The following example returns <table><tbody><tr align="center" valign="top"><td>Cell 1 info</td><td>Cell 2 info</td></tr></tbody></table>:

SELECT XMLELEMENT( name "table",  
         XMLELEMENT( name "tbody", 
           XMLELEMENT( name "tr", 
             XMLATTRIBUTES('center' AS "align", 'top' AS "valign"),
             XMLELEMENT( name "td", 'Cell 1 info' ),
             XMLELEMENT( name "td", 'Cell 2 info' )
           )
         )
       );

The following example returns:'<x>abcdef</x>','<custom_element>abcdef</custom_element>'

CREATE VARIABLE @my_element_name VARCHAR(200);
SET @my_element_name = 'custom_element';
SELECT XMLELEMENT( NAME x, 'abc', 'def' ),
   XMLELEMENT( @my_element_name,'abc', 'def' );