XMLAGG (SQL)
Synopsis
XMLAGG([ ALL | DISTINCT [BY(col-list)] ]
string-expr
[ %FOREACH(col-list) ] [ %AFTERHAVING ])
Arguments
Argument | Description |
---|---|
ALL | Optional — Specifies that XMLAGG returns a concatenated string of all values for string-expr. This is the default if no keyword is specified. |
DISTINCT | Optional — A DISTINCT clause that specifies that XMLAGG returns a concatenated string containing only the unique string-expr values. DISTINCT can specify a BY(col-list) subclause, where col-list can be a single field or a comma-separated list of fields. |
string-expr | An SQL expression that evaluates to a string. Commonly this is the name of a column from which to retrieve data. |
%FOREACH(col-list) | Optional — A column name or a comma-separated list of column names. See SELECT for further information on %FOREACH. |
%AFTERHAVING | Optional — Applies the condition found in the HAVING clause. |
Description
The XMLAGG aggregate function returns a concatenated string of all values from string-expr. The return value is of data type VARCHAR.
-
A simple XMLAGG (or XMLAGG ALL) returns a string containing a concatenated string composed of all the values for string-expr in the selected rows. Rows where string-expr is NULL are ignored.
The following two examples both return the same single value, a concatenated string of all of the values listed in the Home_State column of the Sample.Person table.
SELECT XMLAGG(Home_State) AS All_State_Values FROM Sample.Person
SELECT XMLAGG(ALL Home_State) AS ALL_State_Values FROM Sample.Person
Note that this concatenated string contains duplicate values.
-
An XMLAGG DISTINCT returns a concatenated string composed of all the distinct (unique) values for string-expr in the selected rows: XMLAGG(DISTINCT col1). Rows where string-expr is NULL are ignored. XMLAGG(DISTINCT BY(col2) col1) returns a concatenated string containing only those col1 field values in records where the col2 values are distinct (unique). Note however that the distinct col2 values may include a single NULL as a distinct value.
Rows where string-expr is NULL are omitted from the return value. Rows where string-expr is the empty string ('') are omitted from the return value if at least one non-empty string value is returned. If the only non-NULL string-expr values are the empty string (''), the return value is a single empty string.
XMLAGG does not support data stream fields. Specifying a stream field for string-expr results in an SQLCODE -37.
XML and XMLAGG
One common use of XMLAGG is to tag each data item from a column. This is done by combining XMLAGG and XMLELEMENT as shown in the following example:
SELECT XMLAGG(XMLELEMENT("para",Home_State))
FROM Sample.Person
This results in an output string such as the following:
<para>LA</para><para>MN</para><para>LA</para><para>NH</para><para>ME</para>...
XMLAGG and ORDER BY
The XMLAGG function concatenates values of a table column from multiple rows into a single string. Because an ORDER BY clause is applied to the query result set after all aggregate fields are evaluated, ORDER BY cannot directly affect the sequence of values within this string. Under certain circumstances, XMLAGG results may appear in sequential order, but this ordering should not be relied upon. The values listed within a given aggregate result value cannot be explicitly ordered.