XMLAGG (SQL)

Synopsis

XMLAGG([ ALL | DISTINCT [BY(col-list)] ]
  string-expr 
  [ %FOREACH(col-list) ] [ %AFTERHAVING ])

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

Examples

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:

The query:

SELECT DISTINCT Name, XMLAGG(Make) AS String_Of_Makes
FROM AutoClub WHERE Name = 'Smith,Joe'

returns:

The query:

SELECT DISTINCT Name, XMLAGG(DISTINCT Make) AS String_Of_Makes
FROM AutoClub WHERE Name = 'Smith,Joe'

returns:

See Also

• Aggregate Functions overview

• %DLIST aggregate function

• JSON_ARRAYAGG aggregate function

• LIST aggregate function

• XMLELEMENT function

• SELECT statement