DB2 build xml document from query

In my stored procedure, I want to build a single xml document. The query below creates 100 documents. Is it possible to declare one document which has 100 elements of type elem?

SELECT XMLELEMENT (NAME "elem",
 XMLAttributes(Identifier as "ID", Name AS "name"))
FROM 
(
SELECT a.*
FROM OBJECTS a
FETCH FIRST 100 ROWS ONLY
)

Solution

You are probably looking for the XMLAGG function. It aggregates XML snippets. One use case is to wrapt multiple XMLELEMENTs into a single document. This example is taken from the linked documentation:

   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

In your case, EMPLOYEE would be OBJECTS. The query structure is very similar.