DB2 Version 9.7 for Linux, UNIX, and Windows

XMLAGG aggregate function

Read syntax diagramSkip visual syntax diagram

                      |           .-,----------------------. |      
                      |           V               .-ASC--. | |      
                      '-ORDER BY----| sort-key |--+------+-+-'      

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.

Specifies an expression of data type XML.
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.
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).


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