The XMLAGG function returns an XML sequence containing an item for each non-null value in a set of XML values.
- An expression that returns an XML value.
- ORDER BY
- Specifies the order of the rows from the same grouping set that
are processed in the aggregation. If the ORDER BY clause is not specified,
or if the ORDER BY clause cannot differentiate the order of the sort
key value, the rows in the same grouping set are arbitrarily ordered.
- Specifies a sort key value that is either a column name or an expression. The data type of the column or expression must not be a DATALINK or XML value.
- The ordering is based on the values of the sort keys, which might or might not be used in XML-expression.
- The sum of the length attributes of the sort-key-expressions must not exceed 3.5 gigabytes.
If a collating sequence other than *HEX is in effect when the statement that contains the XMLAGG function is executed and the sort-key-expressions are SBCS data, mixed data, or Unicode data, then the result is obtained by comparing weighted values. The weighted values are derived by applying the collating sequence to the sort-key-expressions.
The function is applied to the set of values derived from the argument values by elimination of null values.
The data type of the result is XML. The result can be null. If the set of values is empty, the result is the null value. Otherwise, the result is an XML sequence containing an item for each value in the set.
- Group employees by their department, generate a "Department" element
for each department with its name as the attribute, nest all the "emp"
elements for employees in each department, and order the "emp" elements
The result of the query would look similar to the following result:
SELECT XMLSERIALIZE(XMLDOCUMENT ( XMLELEMENT(NAME "Department", XMLATTRIBUTES(E.WORKDEPT AS "name"), XMLAGG(XMLELEMENT ( NAME "emp", E.LASTNAME) ORDER BY E.LASTNAME) )) AS CLOB(200)) AS "dept_list" FROM EMPLOYEE E WHERE E.WORKDEPT IN ('C01', 'E21') GROUP BY WORKDEPT
dept_list ---------------------------------- <Department name="C01"> <emp>KWAN</emp> <emp>NICHOLLS</emp> <emp>QUINTANA</emp> </Department> <Department name="E21"> <emp>GOUNOT</emp> <emp>LEE</emp> <emp>MEHTA</emp> <emp>SPENSER</emp> </Department>