ASA SQL User's Guide
Using XML in the Database
Using SQL/XML to obtain query results as XML
The XMLAGG function is used to produce a forest of XML elements from a collection of XML elements. XMLAGG is an aggregate function, and produces a single aggregated XML result for all the rows in the query.
In the following query, XMLAGG is used to generate a <name> element for each row, and the <name> elements are ordered by employee name. The ORDER BY clause is specified to order the XML elements:
SELECT XMLELEMENT( NAME department, XMLATTRIBUTES (dept_id ), XMLAGG( XMLELEMENT( NAME name, emp_lname ) ORDER BY emp_lname ) ) AS dept_list FROM employee GROUP BY dept_id
This query produces the following result:
dept_list |
---|
<department dept_id="100"> <name>Breault</name> <name>Cobb</name> <name>Diaz</name> <name>Driscoll</name> ... </department> |
<department dept_id="200"> <name>Chao</name> <name>Chin</name> <name>Clark</name> <name>Dill</name> ... </department> |
<department dept_id="300"> <name>Bigelow</name> <name>Coe</name> <name>Coleman</name> <name>Davidson</name> ... </department> |
... |
For more information about the XMLAGG function, see XMLAGG function [String].