ASA SQL User's Guide
  Using XML in the Database
    Using SQL/XML to obtain query results as XML

Using the XMLELEMENT function

The XMLELEMENT function constructs an XML element from relational data. You can specify the content of the generated element and if you wish, you can also specify attributes and attribute content for the element.

Generating nested elements 

The following query generates nested XML, producing a <product_info> element for each product, with elements that provide the name, quantity, and description of each product:

SELECT id,
       XMLELEMENT( NAME product_info,
       XMLELEMENT( NAME item_name, product.name ),
       XMLELEMENT( NAME quantity_left, product.quantity ),
       XMLELEMENT( NAME description, product.size || ' ' ||
                            product.color || ' ' ||
                         product.name )
       ) AS results
FROM product
WHERE quantity > 30

This query produces the following result:

<product_info>
 <item_name>Tee Shirt
  </item_name>
 <quantity_left>54
  </quantity_left>
 <description>Medium Orange
  Tee Shirt</description>
</product_info>

<product_info>
 <item_name>Tee Shirt
  </item_name>
 <quantity_left>75
  </quantity_left>
 <description>One size fits
  all Black Tee Shirt
  </description>
</product_info>

<product_info>
 <item_name>Baseball Cap
  </item_name>
 <quantity_left>112
  </quantity_left>
 <description>One size fits
  all Black Baseball Cap
  </description>
</product_info>

Specifying element content 

The XMLELEMENT function allows you to specify the content of an element. The following statement produces an XML element with the content hat.

SELECT id, XMLELEMENT( NAME product_type, 'hat' )
FROM product
WHERE name IN ( 'Baseball Cap', 'Visor' )

Generating elements with attributes 

You can add attributes to the elements by including the attribute-value-expression argument in your query. This argument specifies the attribute name and content. The following statement produces an attribute for the name, color, and unit_price of each item.

SELECT id, XMLELEMENT( NAME item_description,
                       XMLATTRIBUTES( name,
                                      color,
                                      unit_price )
                      ) AS item_description_element
FROM product
WHERE id > 400

Attributes can be named by specifying the attribute-name argument:

SELECT id, XMLELEMENT( NAME item_description,
                       XMLATTRIBUTES ( unit_price AS
                        price ),
                       product.name
                     ) AS products
FROM product
WHERE id > 400

For more information, see XMLELEMENT function [String].