Produces an XML element within a query.

Syntax

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

Parameters

Returns

XML

Remarks

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.

See also

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

Standards and compatibility

Example

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 web site</A>:

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

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' );