Skip to main content

XMLAGG (SQL)

An aggregate function that creates a concatenated string of values.

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.

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:

Name Make Model Year
Smith,Joe Pontiac Firebird 1971
Smith,Joe Saturn SW2 1997
Smith,Joe Pontiac Bonneville 1999
Jones,Scott Ford Mustang 1966
Jones,Scott Mazda Miata 2000

The query:

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

returns:

Name String_Of_Makes
Smith,Joe PontiacSaturnPontiac

The query:

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

returns:

Name String_Of_Makes
Smith,Joe PontiacSaturn

See Also

FeedbackOpens in a new tab