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

Using the XMLFOREST function

XMLFOREST constructs a forest of XML elements. An element is produced for each XMLFOREST argument.

The following query produces an <item_description> element, with <name>, <color>, and <price> elements:

SELECT id, XMLELEMENT( NAME item_description,
                       XMLFOREST( name,
                                  color,
                                  unit_price AS price )
                      ) AS product_info
FROM product
WHERE id > 400

The following result is generated by this query:

<item_description>
 <name>Baseball Cap</name>
 <color>White</color>
 <price>10.00</price>
</item_description>

<item_description>
 <name>Visor</name>
 <color>White</color>
 <price>7.00</price>
</item_description>

<item_description>
 <name>Visor</name>
 <color>Black</color>
 <price>7.00</price>
</item_description>

For more information, see XMLFOREST function [String].