![]() | |
|
XMLAGG aggregate function
>>-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.
Example: Construct a department element for each department, containing a list of employees sorted by last name:
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 WORKDEPTThis 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>
![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |