Caché SQL Reference
XMLAGG
[Back] 
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

An aggregate function that creates a concatenated string of values.
Synopsis
XMLAGG([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])
Arguments
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, with a default length of 4096.
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 concatenated values are, by default, listed in the RowId sequence. You can use an ORDER BY clause to sequence these values in ascending collation sequence. This use of ORDER BY is subject to various conditions and restrictions, as described in the With ORDER BY section of the Aggregate Functions overview page.
Related Aggregate Functions
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