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.
XML and XMLAGG
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:
XMLAGG and ORDER BY
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.
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'
Content Date/Time: 2019-07-18 06:47:30