The
XMLTABLE function returns a result table from the evaluation of XQuery
expressions, possibly using specified input arguments as XQuery variables.
Each sequence item in the result sequence of the row XQuery expression
represents a row of the result table.
>>-XMLTABLE--(--+------------------------------+---------------->
'-xmlnamespaces-declaration--,-'
>--row-xquery-expression-constant------------------------------->
>--+--------------------------------------------------+--------->
| .-,-----------------------. |
| .-BY REF-. V | |
'-PASSING--+--------+----| row-xquery-argument |-+-'
>--+--------------------------------------------------------------------+-><
| .-,--------------------------------------------------. |
| V (1) | |
'-COLUMNS----+-| xml-table-regular-column-definition |----+-----+--)-'
'-| xml-table-ordinality-column-definition |-'
row-xquery-argument
(2)
|--xquery-variable-expression------AS--identifier--+--------+---|
'-BY REF-'
xml-table-regular-column-definition
|--column-name--data-type--+--------+--+----------------+------->
'-BY REF-' '-default-clause-'
>--+-----------------------------------------+------------------|
'-PATH--column-xquery-expression-constant-'
xml-table-ordinality-column-definition
|--column-name--FOR ORDINALITY----------------------------------|
Notes:
- The xml-table-ordinality-column-definition clause
must not be specified more than once (SQLSTATE 42614).
- The data type of the expression cannot be DECFLOAT.
The schema is SYSIBM. The function name cannot be specified
as a qualified name.
- xmlnamespaces-declaration
- Specifies one or more XML namespace declarations that become part
of the static context of the row-xquery-expression-constant and
the column-xquery-expression-constant. The
set of statically known namespaces for XQuery expressions
which are arguments of XMLTABLE is the combination of the pre-established
set of statically known namespaces and the namespace declarations
specified in this clause. The XQuery prolog within an XQuery expression
may override these namespaces.
If xmlnamespaces-declaration is
not specified, only the pre-established set of statically known namespaces
apply to the the XQuery expressions.
- row-xquery-expression-constant
- Specifies an SQL character string constant that is interpreted
as an XQuery expression using supported XQuery language syntax. The
constant string is converted directly to UTF-8 without conversion
to the database or section code page. The XQuery expression executes
using an optional set of input XML values, and returns an output XQuery
sequence where a row is generated for each item in the sequence. The
value for row-xquery-expression-constant must
not be an empty string or a string of all blanks (SQLSTATE 10505).
- PASSING
- Specifies input values and the manner in which these values are
passed to the XQuery expression specified by row-xquery-expression-constant.
By default, every unique column name that is in the scope where the
function is invoked is implicitly passed to the XQuery expression
using the name of the column as the variable name. If an identifier in
a specified row-xquery-argument matches an in-scope column name, then
the explicit row-xquery-argument is passed to the XQuery expression
overriding that implicit column.
- BY REF
- Specifies that any XML input arguments are, by default, passed
by reference. When XML values are passed by reference, the XQuery
evaluation uses the input node trees, if any, directly from the specified
input expressions, preserving all properties, including the original
node identities and document order. If two arguments pass the same
XML value, node identity comparisons and document ordering comparisons
involving some nodes contained between the two input arguments might
refer to nodes within the same XML node tree.
This clause has
no impact on how non-XML values are passed. The non-XML values create
a new copy of the value during the cast to XML.
- row-xquery-argument
- Specifies an argument that is to be passed to the XQuery expression
specified by row-xquery-expression-constant.
An argument specifies a value and the manner in which that value is
to be passed. The argument includes an SQL expression that is evaluated
before passing the result to the XQuery expression.
- If the resulting value is of type XML, it becomes an input-xml-value.
A null XML value is converted to an XML empty sequence.
- If the resulting value is not of type XML, it must be castable
to the XML data type. A null value is converted to an XML empty sequence.
The converted value becomes an input-xml-value.
When the row-xquery-expression-constant is
evaluated, an XQuery variable is presented with a value equal to input-xml-value and
a name specified by the AS clause. - xquery-variable-expression
- Specifies an SQL expression whose value is available to the XQuery
expression specified by row-xquery-expression-constant during
execution. The expression cannot contain a NEXT VALUE expression,
PREVIOUS VALUE expression (SQLSTATE 428F9), or an OLAP function (SQLSTATE
42903). The data type of the expression cannot be DECFLOAT.
- AS identifier
- Specifies that the value generated by xquery-variable-expression will
be passed to row-xquery-expression-constant as
an XQuery variable. The variable name will be identifier.
The leading dollar sign ($) that precedes variable names in the XQuery
language is not included in identifier.
The identifier must be a valid XQuery variable name and is restricted
to an XML NCName. The identifier must not be greater than 128 bytes
in length. Two arguments within the same PASSING clause cannot use
the same identifier (SQLSTATE 42711).
- BY REF
- Indicates that an XML input value is to be passed by reference.
When XML values are passed by reference, the XQuery evaluation uses
the input node trees, if any, directly from the specified input expressions,
preserving all properties, including the original node identities
and document order. If two arguments pass the same XML value, node
identity comparisons and document ordering comparisons involving some
nodes contained between the two input arguments might refer to nodes
within the same XML node tree. If BY REF is not specified following
an xquery-expression-variable, XML arguments
are passed by way of the default passing mechanism that is provided
through the syntax that follows the PASSING keyword. This option cannot
be specified for non-XML values (SQLSTATE 42636). When a non-XML value
is passed, the value is converted to XML; this process creates a copy.
- COLUMNS
- Specifies the output columns of the result table.
If this clause is not specified, a single unnamed column of data
type XML is returned by reference, with the value based on the sequence
item from evaluating the XQuery expression in the row-xquery-expression-constant (equivalent
to specifying PATH '.'). To reference the result column, a column-name must
be specified in the correlation-clause following
the function.
- xml-table-regular-column-definition
- Specifies the output columns of the result table including the
column name, data type, XML passing mechanism and an XQuery expression
to extract the value from the sequence item for the row
- column-name
- Specifies the name of the column in the result table. The name
cannot be qualified and the same name cannot be used for more than
one column of the table (SQLSTATE 42711).
- data-type
- Specifies the data type of the column. See CREATE TABLE for the
syntax and a description of types available. A data-type may
be used in XMLTable if there is a supported XMLCAST from the XML data
type to the specified data-type.
- BY REF
- Specifies that XML values are returned by reference for columns
of data type XML. By default, XML values are returned BY REF. When
XML values are returned by reference, the XML value includes the input
node trees, if any, directly from the result values, and preserves
all properties, including the original node identities and document
order. This option cannot be specified for non-XML columns (SQLSTATE
42636). When a non-XML column is processed, the value is converted
from XML; this process creates a copy.
- default-clause
- Specifies a default value for the column. See CREATE TABLE for
the syntax and a description of the default-clause.
For XMLTABLE result columns, the default is applied when the processing
the XQuery expression contained in column-xquery-expression-constant returns
an empty sequence.
- PATH column-xquery-expression-constant
- Specifies an SQL character string constant that is interpreted
as an XQuery expression using supported XQuery language syntax. The
constant string is converted directly to UTF-8 without conversion
to the database or section code page. The column-xquery-expression-constant specifies
an XQuery expression that determines the column value with respect
to an item that is the result of evaluating the XQuery expression
in row-xquery-expression-constant. Given
an item from the result of processing the row-xquery-expression-constant as
the externally provided context item, the column-xquery-expression-constant is
evaluated, returning an output sequence. The column value is determined
based on this output sequence as follows.
- If the output sequence contains zero items, the default-clause provides
the value of the column.
- If an empty sequence is returned and no default-clause was
specified, a null value is assigned to the column.
- If a non-empty sequence is returned, the value is XMLCAST to the data-type specified
for the column. An error could be returned from processing this XMLCAST.
The value for column-xquery-expression-constant must
not be an empty string or a string of all blanks (SQLSTATE 10505).
If this clause is not specified, the default XQuery expression is
simply the column-name.
- xml-table-ordinality-column-definition
- Specifies the ordinality column of the result table.
- column-name
- Specifies the name of the column in the result table. The name
cannot be qualified and the same name cannot be used for more than
one column of the table (SQLSTATE 42711).
- FOR ORDINALITY
- Specifies that column-name is the ordinality
column of the result table. The data type of this column is BIGINT.
The value of this column in the result table is the sequential number
of the item for the row in the resulting sequence from evaluating
the XQuery expression in row-xquery-expression-constant.
If the evaluation of any of the XQuery expressions results
in an error, then the XMLTABLE function returns the XQuery error (SQLSTATE
class '10').
Example
List
as a table result the purchase order items for orders with a status
of 'NEW'.
SELECT U."PO ID", U."Part #", U."Product Name",
U."Quantity", U."Price", U."Order Date"
FROM PURCHASEORDER P,
XMLTABLE('$po/PurchaseOrder/item' PASSING P.PORDER AS "po"
COLUMNS "PO ID" INTEGER PATH '../@PoNum',
"Part #" CHAR(10) PATH 'partid',
"Product Name" VARCHAR(50) PATH 'name',
"Quantity" INTEGER PATH 'quantity',
"Price" DECIMAL(9,2) PATH 'price',
"Order Date" DATE PATH '../@OrderDate'
) AS U
WHERE P.STATUS = 'Unshipped'