XMLAGG([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])
|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.|
The XMLAGG aggregate function returns a concatenated string of all values from string-expr. The return value is of data type VARCHAR, with a default length of 4096.
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:
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.
The following example creates a concatenated string of all of the distinct values found in the FavoriteColors column of the Sample.Person table. Thus every row has the same value for the All_Colors column. Note that while some rows have a NULL value for FavoriteColors, this value is not included in the concatenated string. Data values are returned in internal format.
SELECT Name,FavoriteColors, XMLAGG(DISTINCT FavoriteColors) AS All_Colors_In_Table FROM Sample.Person ORDER BY FavoriteColors
The following example returns concatenated strings of Home_State values that begin with “A”. It returns the distinct Home_State values (DISTINCT Home_State); the Home_State values corresponding to distinct Home_City values (DISTINCT BY(Home_City) Home_State), which may possibly including one unique NULL for Home_City; and all Home_State values:
SELECT XMLAGG(DISTINCT Home_State) AS DistStates, XMLAGG(DISTINCT BY(Home_City) Home_State) AS DistCityStates, XMLAGG(Home_State) AS AllStates FROM Sample.Person WHERE Home_State %STARTSWITH 'A'
The following example creates a concatenated string of all of the distinct values found in the Home_City column for each of the states. Every row from the same state contains a list of all of the distinct city values for that state:
SELECT Home_State, Home_City, XMLAGG(DISTINCT Home_City %FOREACH(Home_State)) AS All_Cities_In_State FROM Sample.Person ORDER BY Home_State
The following example uses the %AFTERHAVING keyword. It returns a row for each Home_State that contains at least one Name value that fulfills the HAVING clause condition (a name that begins with either “C” or “K”). The first XMLAGG function returns a concatenated string consisting of all of the names for that state. The second XMLAGG function returns a concatenated string consisting of only those names that fulfill the HAVING clause condition:
SELECT Home_State, XMLAGG(Name) AS AllNames, XMLAGG(Name %AFTERHAVING) AS HaveClauseNames FROM Sample.Person GROUP BY Home_State HAVING Name LIKE 'C%' OR Name LIKE 'K%' ORDER BY Home_state
For the following examples, suppose we have the following table, AutoClub:
SELECT DISTINCT Name, XMLAGG(Make) AS String_Of_Makes FROM AutoClub WHERE Name = 'Smith,Joe'
SELECT DISTINCT Name, XMLAGG(DISTINCT Make) AS String_Of_Makes FROM AutoClub WHERE Name = 'Smith,Joe'
Aggregate Functions overview
%DLIST aggregate function
JSON_ARRAYAGG aggregate function
LIST aggregate function