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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using XML in the Database » Using SQL/XML to obtain query results as XML

Using SQL/XML to obtain query results as XML Next Page

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:

                   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">
<Departments DepartmentID="200">
<Departments DepartmentID="300">

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