XMLAGG aggregate function

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

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

XML-expression

Specifies an expression of data type XML.

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.

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

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).

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>