Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Using the XMLAGG function

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 Departments,
                   XMLATTRIBUTES (DepartmentID ),
                   XMLAGG( XMLELEMENT( NAME name,
                             Surname )
                             ORDER BY Surname )
                  ) AS department_list
FROM Employees
GROUP BY DepartmentID
ORDER BY DepartmentID;

This query produces the following result:

<Departments DepartmentID="100">
 <name>Breault</name>
 <name>Cobb</name>
 <name>Diaz</name>
 <name>Driscoll</name>
 ...
</Departments>

<Departments DepartmentID="200">
 <name>Chao</name>
 <name>Chin</name>
 <name>Clark</name>
 <name>Dill</name>
 ...
</Departments>

<Departments DepartmentID="300">
 <name>Bigelow</name>
 <name>Coe</name>
 <name>Coleman</name>
 <name>Davidson</name>
 ...
</Departments>

...

For more information about the XMLAGG function, see XMLAGG function [Aggregate].