XMLAGG aggregate function

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

The schema is SYSIBM. The function name cannot be specified as a qualified name.

XML-expression: Specifies an expression of data type XML. The data type of XML-expression cannot be a BINARY or VARBINARY type (SQLSTATE 42884).
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 omitted, or if the ORDER BY clause cannot distinguish the order of the column data, the rows in the same grouping set are arbitrarily ordered.
sort-key: The sort key can be a column name or a sort-key-expression. Note that if the sort key is a constant, it does not refer to the position of the output column (as in the ordinary ORDER BY clause), but it is simply a constant, which implies no sort key.

The data type of the result is XML.

The function is applied to the set of values derived from the argument values by the elimination of null values.

If the XML-expression argument can be null, 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.

If a SELECT clause includes an ARRAY_AGG function, then all invocations of ARRAY_AGG, LISTAGG, XMLAGG, and XMLGROUP functions in the same SELECT clause must specify the same order or not specify an order (SQLSTATE 428GZ).

Notes

Support in OLAP expressions: XMLAGG cannot be used as a column function of an OLAP aggregation function (SQLSTATE 42601).

Example

Construct a department element for each department, containing a list of employees sorted by last name.

   SELECT XMLSERIALIZE(
     CONTENT XMLELEMENT(
       NAME "Department", XMLATTRIBUTES(
         E.WORKDEPT AS "name"
       ),
       XMLAGG(
         XMLELEMENT(
           NAME "emp", E.LASTNAME
         )
         ORDER BY E.LASTNAME
       )
     )
     AS CLOB(110)
   )
   AS "dept_list"
   FROM EMPLOYEE E
   WHERE E.WORKDEPT IN ('C01','E21')
   GROUP BY WORKDEPT

This query produces 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>

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