XMLAGG

The XMLAGG function returns an XML sequence containing an item for each non-null value in a set of XML values.

XML-expression

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.

sort-key-expression: 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.

Example

Note: XMLAGG does not insert blank spaces or new line characters in the output. All example output has been formatted to enhance readability.

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 by LASTNAME.

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

The result of the query would look similar to the following result:

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>