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 as name,
                                  Color as color,
                                  UnitPrice AS price )
                      ) AS product_info
FROM Products
WHERE ID > 400;

The following result is generated by this query:

ID

product_info

401
<item_description>
 <name>Baseball Cap</name>
 <color>White</color>
 <price>10.00</price>
</item_description>
500
<item_description>
 <name>Visor</name>
 <color>White</color>
 <price>7.00</price>
</item_description>
501
<item_description>
 <name>Visor</name>
 <color>Black</color>
 <price>7.00</price>
</item_description>
... ...

For more information, see XMLFOREST function [String].