1.14.5 SQL/XML set functions

Organization of this subsection
(1) XMLAGG

(1) XMLAGG

(a) Function

Generates an XML type value from the concatenation of values from a collection of rows values where each row of the argument specifies the XML type.

(b) Format

XMLAGG ( value-expression
       [RETURNING SEQUENCE])

(c) Operands

Specifies the concatenated XML type value expression.

The following can be specified:

This format is supported only for compatibility with ISO standards. It has no effect on the format of the value of the concatenation result.

(d) Rules
  1. The result is an XQuery sequence whose value is the XML type.
  2. The order of concatenation is not guaranteed.
  3. Those rows for which the result of the value expression is the null value are ignored.
  4. The result is the null value if the target is empty or consists solely of null values.
  5. For INSERT and UPDATE statements, the result of this function (or XML type values based on the result) cannot be stored in a column.

For details about other rules, see the rules in 2.14 Set functions.

(e) Example

From the bookinfo column of every row of BOOK_MANAGEMENT_TABLE, retrieve book information of the same category as the book whose title is SQL Explained.

 SELECT XMLSERIALIZE(
   XMLQUERY(
     '$BOOKS/bookinfo[category=$BOOKS/bookinfo[title="SQL Explained"]/category]'
     PASSING BY VALUE XMLAGG(bookinfo) AS BOOKS
     RETURNING SEQUENCE BY VALUE EMPTY ON EMPTY)
 AS VARCHAR(32000))
   FROM BOOK_MANAGEMENT_TABLE