The XMLGROUP function returns an XML value with a single
XQuery document node containing one top-level element node. This is
an aggregate expression that will return a single-rooted XML document
from a group of rows where each row is mapped to a row subelement.
.-,-------------------------------------.
V |
>>-XMLGROUP--(----expression--+-----------------------+-+------->
'-AS-- qname-identifier-'
>--+--------------------------------------+--------------------->
| .-,----------------------. |
| V .-ASC--. | |
'-ORDER BY----| sort-key |--+------+-+-'
'-DESC-'
>--+--------------------------------------------+--)-----------><
| .--------------------------------. |
| V (1) .-ROW-- "row"----. | |
'-OPTION----------+-+-ROW-- row-name-+---+-+-'
| .-ROOT-- "rowset"--. |
+-+-ROOT-- root-name-+-+
'-AS ATTRIBUTES--------'
Notes:
- The same clause must not be specified more than once.
The schema is SYSIBM. The function name cannot be specified
as a qualified name.
- expression
- The content of each generated XML element node (or the value of
each generated attribute) is specified by an expression. The data
type of expression cannot be a structured
type (SQLSTATE 42884). The expression can be any SQL expression. If
the expression is not a simple column reference, a qname-identifier must
be specified.
- AS qname-identifier
- Specifies the XML element name or attribute name as an SQL identifier.
The qname-identifier must be of the form
of an XML qualified name, or QName (SQLSTATE 42634). See the W3C XML
namespace specifications for more details on valid names. If the name
is qualified, the namespace prefix must be declared within the scope
(SQLSTATE 42635). If qname-identifier is
not specified, expression must be a column
name (SQLSTATE 42703). The element name or attribute name is created
from the column name using the fully escaped mapping from a column
name to an QName.
- OPTION
- Specifies additional options for constructing the XML value. If
no OPTION clause is specified, the default behavior applies.
- ROW row-name
- Specifies the name of the element to which each row is mapped.
If this option is not specified, the default element name is "row".
- ROOT root-name
- Specifies the name of the root element node. If this option is
not specified, the default root element name is "rowset"
- AS ATTRIBUTES
- Specifies that each expression is mapped to an attribute value
with column name or qname-identifier serving
as the attribute name.
- ORDER BY
- Specifies the order of the rows from the same grouping set that
are processed in the aggregation. If the ORDER BY clause is omitted,
or if the ORDER BY clause cannot distinguish the order of the column
data, the rows in the same grouping set are arbitrarily ordered.
- sort-key
- The sort key can be a column name or a sort-key-expression.
Note that if the sort key is a constant, it does not refer to the
position of the output column (as in the ordinary ORDER BY clause),
but it is simply a constant, which implies no sort key.
Rules
- If
a SELECT clause includes an ARRAY_AGG function, then all invocations
of ARRAY_AGG, LISTAGG, XMLAGG, and XMLGROUP functions in the same
SELECT clause must specify the same order or not specify an order
(SQLSTATE 428GZ).
Notes
The default behavior defines a simple
mapping between a result set and an XML value. Some additional notes
about function behavior apply:
- By default, each row is transformed into an XML element named
"row" and each column is transformed into a nested element with the
column name serving as the element name.
- The null handling behavior is NULL ON NULL. A null value in a
column maps to the absence of the subelement. If all column values
are null, no row element will be generated.
- The binary encoding scheme for BLOB and FOR BIT DATA data types
is base64Binary encoding.
- By default, the elements corresponding to the rows in a group
are children of a root element named "rowset".
- The order of the row subelements in the root element will be the
same as the order in which the rows are returned in the query result
set.
- A document node will be added implicitly to the root element to
make the XML result a well-formed single-rooted XML document
Examples
Assume the following table T1
with integer columns C1 and C2 that contain numeric data stored in
a relational format.
C1 C2
----------- -----------
1 2
- 2
1 -
- -
4 record(s) selected.
- The following example shows an XMLGroup query and output fragment
with default behavior, using a single top-level element to represent
the table:
SELECT XMLGROUP(C1, C2)FROM T1
<rowset>
<row>
<C1>1</C1>
<C2>2</C2>
</row>
<row>
<C2>2</C2>
</row>
<row>
<C1>1</C1>
</row>
</rowset>
1 record(s) selected.
- The following example shows an XMLGroup query and output fragment
with attribute centric mapping. Instead of appearing as nested elements
as in the previous example, relational data is mapped to element attributes:
SELECT XMLGROUP(C1, C2 OPTION AS ATTRIBUTES) FROM T1
<rowset>
<row C1="1" C2="2"/>
<row C2="2"/>
<row C1="1"/>
</rowset>
1 record(s) selected.
- The following example shows an XMLGroup query and output fragment
with the default <rowset> root element replaced by <document>
and the default <row> element replaced by <entry>. Columns
C1 and C2 are returned as <column1> and <column2> elements,
and the return set is ordered by column C1:
SELECT XMLGROUP(
C1 AS "column1", C2 AS "column2"
ORDER BY C1 OPTION ROW "entry" ROOT "document")
FROM T1
<document>
<entry>
<column1>1</column1>
<column2>2</column2>
</entry>
<entry>
<column1>1</column1>
</entry>
<entry>
<column2>2</column2>
</entry>
</document>