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 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>