ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

XMLELEMENT function [String]

Function 

Produces an XML element within a query.

Syntax 

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

Parameters 

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,

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

returns

<x>abcdef</x>

Usage 

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

Element content is always quoted unless the data type is XML. Invalid element and attribute names are also quoted.

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 wish 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 XMLELEMENT.

Standards and compatibility 

• Part of the SQL/XML draft standard.

• Sybase    Not supported by Adaptive Server Enterprise.

See also 

Using the XMLELEMENT function

XMLFOREST function [String]

Example 

The following statement 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 product p
WHERE id > 400