>>-XMLAGG-------------------------------------------------------> >--(--XML-expression--+--------------------------------------+--)->< | .-,----------------------. | | V .-ASC--. | | '-ORDER BY----| sort-key |--+------+-+-' '-DESC-'
The schema is SYSIBM. The function name cannot be specified as a qualified name.
The XMLAGG function returns an XML sequence containing an item for each non-null value in a set of XML values.
The data type of the result is XML.
The function is applied to the set of values derived from the argument values by the elimination of null values.
If the XML-expression argument can be null, the result can be null. If the set of values is empty, the result is the null value. Otherwise, the result is an XML sequence containing an item for each value in the set.
XMLAGG cannot be used as a column function of an OLAP aggregation function (SQLSTATE 42601).
If a SELECT clause includes an ARRAY_AGG function, then all invocations of ARRAY_AGG, LISTAGG, XMLAGG, and XMLGROUP functions in the same SELECT clause must specify the same order or not specify an order (SQLSTATE 428GZ).
Example:
SELECT XMLSERIALIZE(
CONTENT XMLELEMENT(
NAME "Department", XMLATTRIBUTES(
E.WORKDEPT AS "name"
),
XMLAGG(
XMLELEMENT(
NAME "emp", E.LASTNAME
)
ORDER BY E.LASTNAME
)
)
AS CLOB(110)
)
AS "dept_list"
FROM EMPLOYEE E
WHERE E.WORKDEPT IN ('C01','E21')
GROUP BY WORKDEPT
This query produces the following
result: dept_list
-----------------------...
<Department name="C01">
<emp>KWAN</emp>
<emp>NICHOLLS</emp>
<emp>QUINTANA</emp>
</Department>
<Department name="E21">
<emp>GOUNOT</emp>
<emp>LEE</emp>
<emp>MEHTA</emp>
<emp>SPENSER</emp>
</Department>