InterSystems SQL Reference
An aggregate function that creates a concatenated string of values.
XMLAGG([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])
||Optional Specifies that XMLAGG returns a concatenated string of all values for string-expr. This is the default if no keyword is specified.
||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.
||An SQL expression that evaluates to a string. Commonly this is the name of a column from which to retrieve data.
||Optional A column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.
||Optional Applies the condition found in the HAVING clause.
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
SELECT XMLAGG(ALL Home_State) AS ALL_State_Values
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.
does not support data stream fields. Specifying a stream field for string-expr
results in an SQLCODE -37.
One common use of XMLAGG
is to tag each data item from a column. This is done by combining XMLAGG
as shown in the following example:
This results in an output string such as the following:
Related Aggregate Functions
returns a string of concatenated values.
returns a comma-separated list of values.
returns an InterSystems IRIS list containing an element for each value.
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.
XMLAGG(DISTINCT FavoriteColors) AS All_Colors_In_Table
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
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
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:
XMLAGG(Name) AS AllNames,
XMLAGG(Name %AFTERHAVING) AS HaveClauseNames
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'