XMLAGG aggregate function

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

sort-key

The schema is SYSIBM.

XML-expression

An expression that returns an XML value.

Unlike the arguments for other aggregate functions, a scalar fullselect is allowed in XML-expression.

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

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 LOB or an XML value. A character string expression cannot have a length greater than 4000 bytes. If the sort key value is a constant, it does not refer to the position of the output column (as in the ordinary ORDER BY clause), but is simply a constant, which implies no sort key.

The ordering is based on the values of the sort keys, which might or might not be used in XML-expression.

If the sort key value is a character string that uses an encoding scheme other than Unicode, the ordering might be different. For example, a column PRODCODE uses EBCDIC. For two values, ('P001' and 'PA01'), relationship 'P001' > 'PA01' is true in EBCDIC, whereas 'P001' < 'PA01' is true in UTF-8. If the same sort key values are used in XML-expression, use the CAST specification to convert the sort key to Unicode to keep the ordering of XML values consistent with that of the sort key.

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

The result can be null; if all XML-expression arguments are null. If the function is applied to an empty set, the result is the null value. Otherwise, the result is an XML sequence that contains an item for each value in the set.

Start of change The following restrictions apply to XMLAGG: End of change

XMLAGG cannot be used as part of an OLAP specification.
A SELECT clause that includes an invocation of the XMLAGG function must not also include an invocation of the ARRAY_AGG function or the LISTAGG function.

Example: 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 'lname.'

   SELECT XMLSERIALIZE(XMLDOCUMENT
         ( XMLELEMENT
         ( NAME "Department",
           XMLATTRIBUTES ( e.dept AS "name" ), 
           XMLAGG ( XMLELEMENT ( NAME "emp", e.lname)
                    ORDER BY e.lname)
                  ) ) AS "dept_list"
                      AS CLOB(1M))
     FROM employees e
     GROUP BY dept;

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

   dept_list
 ---------------------------------------------
<Department name="Accounting">
  <emp>SMITH</emp>
  <emp>Yates</emp>
 </Department>
 <Department name="Shipping">
  <emp>Martin</emp>
  <emp>Oppenheimer</emp>
 </Department>
------------------------------------------------