
The XMLAGG function returns an XML sequence containing an item for each non-null value in a set of XML values.
>>-XMLAGG--(--XML-expression -+---------------------------------------------+--)->< | .-,-----------------------------. | | V .-ASC--. | | '-ORDER BY----sort-key-expression--+------+-+-' '-DESC-'
If a collating sequence other than *HEX is in effect when the statement that contains the XMLAGG function is executed and the sort-key-expressions are SBCS data, mixed data, or Unicode data, then the result is obtained by comparing weighted values. The weighted values are derived by applying the collating sequence to the sort-key-expressions.
The function is applied to the set of values derived from the argument values by elimination of null values.
The data type of the result is XML. The result can be null. If the function is applied to an empty set, the result is the null value. Otherwise, the result is an XML sequence containing an item for each value in the set.
SELECT XMLSERIALIZE(XMLDOCUMENT ( 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 WORKDEPTThe result of the query would look similar to 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>
