XMLAgg() aggregate

The XMLAgg() aggregate combines a set of XML inputs into a single XML object. XMLAgg() is a user-defined aggregate (UDA). To display information about the XMLAgg() aggregate, use the \da (describe aggregate) option for the nzsql command or the SHOW AGGREGATE command.

Syntax

The XMLAgg() aggregate has the following syntax:

XML = XMLAgg(Set(XML) inputs);

The inputs value specifies the set of XML inputs to aggregate into a single XML object.

Returns

The aggregate returns type XML: a compiled representation of a single XML object that is aggregated from a set of XML inputs.

Example

Consider the following example:

SELECT
      XMLElement('Departments', XMLAGG(
         XMLElement('Dept', XMLConcat(
         XMLElement('Number', d.deptno),
         XMLElement('Name', d.deptname),
         XMLElement('Location', d.deptloc)))))
from
      departments d;

Assuming that the query returns three rows of data, a possible return value is as follows:

<Departments>
      <Dept>
         <Number>10</Number>
         <Name>MARKETING</Name>
         <Location>BOSTON</Location>
      </Dept>
      <Dept>
         <Number>20</Number>
         <Name>HR</Name>
         <Location>BOSTON</Location>
      </Dept>
      <Dept>
         <Number>30</Number>
         <Name>SALES</Name>
         <Location>NEW YORK</Location>
      </Dept>
</Departments>