The XMLROW function returns an XML value with a single
XQuery document node containing one top-level element node.
.-,-------------------------------------.
V |
>>-XMLROW--(----expression--+-----------------------+-+--------->
'-AS-- qname-identifier-'
>--+------------------------------------------+--)-------------><
| .------------------------------. |
| V (1) .-ROW-- "row"----. | |
'-OPTION----------+-+-ROW-- row-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 is specified by
an expression. The data type of the expression cannot be a structured
type (SQLSTATE 42884). The expression can be any SQL expression. If
the expression is not a simple column reference, an element name 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.
- AS ATTRIBUTES
- Specifies that each expression is mapped to an attribute value
with column name or qname-identifier serving
as the attribute name.
- 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".
Notes
By default, each row in the result
set is mapped to an XML value as follows:
- Each row is transformed into an XML element named "row" and each
column is transformed into a nested element with the column name 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, a null value is returned by the function.
- The binary encoding scheme for BLOB and FOR BIT DATA data types
is base64Binary encoding.
- A document node will be added implicitly to the row element to
make the XML result a well-formed single-rooted XML document.
Examples
Assume the following table T1
with 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 XMLRow query and output fragment
with default behavior, using a sequence of row elements to represent
the table:
SELECT XMLROW(C1, C2) FROM T1
<row><C1>1</C1><C2>2</C2></row>
<row><C2>2</C2></row>
<row><C1>1</C1></row>
4 record(s) selected.
- The following example shows an XMLRow 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 XMLROW(C1, C2 OPTION AS ATTRIBUTES) FROM T1
<row C1="1" C2="2"/>
<row C2="2"/>
<row C1="1"/>
4 record(s) selected.
- The following example shows an XMLRow query and output fragment
with the default <row> element replaced by <entry>. Columns
C1 and C2 are returned as <column1> and <column2> elements,
and the total of C1 and C2 is returned inside a <total> element:
SELECT XMLROW(
C1 AS "column1", C2 AS "column2",
C1+C2 AS "total" OPTION ROW "entry")
FROM T1
<entry><column1>1</column1><column2>2</column2><total>3</total></entry>
<entry><column2>2</column2></entry>
<entry><column1>1</column1></entry>
4 record(s) selected.