XMLTABLE table function
The XMLTABLE function returns a result table from the evaluation of XQuery expressions, possibly by using specified input arguments as XQuery variables. Each item in the result sequence of the row XQuery expression represents one row of the result table.
row-xquery-argument
xml-table-regular-column-definition
xml-table-ordinality-column-definition
The schema is SYSIBM.
The function name cannot be specified as a qualified name.
- xmlnamespaces-declaration
- Specifies one or more XML namespace declarations, using the XMLNAMESPACES
function, 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
can override these namespaces.
If xmlnamespaces-declaration is not specified, only the pre-established set of statically known namespaces apply to 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. row-xquery-expression-constant cannot be an XQuery updating expression. This expression determines the number of rows in the result table. The expression is evaluated using the optional set of input XML values that is specified in row-xquery-argument, and returns an output XQuery sequence where one row is generated for each item in the sequence. If the sequence is empty, the result of XMLTABLE is an empty table. row-xquery-expression-constant must not contain an empty string or a string of all blanks.
- PASSING
- Specifies input values and the manner in which these values are
passed to row-xquery-expression-constant.
- 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 exist, directly from the specified
input expressions and preserves all properties, including the original
node identities and document order.
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. row-xquery-argument is
an SQL expression that returns a value that is not a ROWID, LOB, DATE,
TIME, TIMESTAMP, BINARY, VARBINARY, REAL, DECFLOAT, or character string
with FOR BIT DATA attribute.
How row-xquery-argument is used in the XQuery expression depends on whether the argument is specified as an xquery-context-item-expression or an xquery-variable-expression.
If the data type of row-xquery-argument is not XML, the result of the expression for the argument is implicitly cast to XML. A null value is converted to an XML empty sequence if the argument is xquery-variable-expression.
row-xquery-argument must not contain NEXT VALUE or PREVIOUS VALUE expressions or OLAP specifications.
- xquery-context-item-expression
- An expression that returns a value that is XML, integer, decimal,
or a character or graphic string that is not a LOB. xquery-context-item-expression must
not be a character string that is bit data.
xquery-context-item-expression specifies the initial context item for the row-xquery-expression. The value of the initial context item is the result of xquery-context-item-expression cast to XML. xquery-context-item-expression must not be specified more than one time.
- 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 must returns a value that is XML, integer,
decimal, or a character or graphic string that is not a LOB.
xquery-variable-expression specifies an argument that will be passed to row-xquery-expression-constant as an XQuery variable. If xquery-variable-expression is a null value, the XQuery variable is set to an XML empty sequence. The scope of the XQuery variables that are created from the PASSING clause is the XQuery expression specified by row-xquery-expression-constant.
- 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 not be greater than 128 bytes in length. Two arguments within the same PASSING clause cannot use the same identifier.
- COLUMNS
- Specifies the output columns of the result table including the
column name, data type, and how the column value is computed for each
row. If this clause is not specified, a single unnamed column of data
type XML is returned, 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 one output column of the result table including the
column name, data type, 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.
- data-type
- Specifies the data type of the column. See CREATE TABLE for the syntax and a description of types available. A data-type can be used in XMLTABLE if there is a supported XMLCAST from the XML data type to the specified data-type.
- 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 of the XQuery expression contained in column-xquery-expression-constant returns an empty sequence. This default value will not be inherited by declared global temporary tables even when the INCLUDING COLUMN DEFAULTS clause is specified in the definition of the declared global temporary table.
- PATH column-xquery-expression-constant
- Specifies an SQL character string constant that is interpreted
as an XQuery expression
using supported XQuery language
syntax. 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 and returns 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 cast to the data-type specified for the column using the XMLCAST expression. An error could be returned from processing this XMLCAST.
- 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.
- 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.
The result of the function is a table. The encoding scheme of the table is Unicode. If the evaluation of any of the XQuery expressions results in an error, the XMLTABLE function returns the XQuery error.
SELECT U."PO ID", U."Part #", U."Product Name",
U."Quantity", U."Price", U."Order Date"
FROM PURCHASEORDER P,
XMLTABLE(XMLNAMESPACES('http://podemo.org' AS "pod"),
'$po/PurchaseOrder/itemlist/item' PASSING P.PORDER as "po"
COLUMNS "PO ID" INTEGER PATH '../../@POid',
"Part #" CHAR(6) PATH 'product/@pid',
"Product Name" CHAR(50) PATH 'product/pod:name',
"Quantity" INTEGER PATH 'quantity',
"Price" DECIMAL(9,2) PATH 'product/pod:price',
"Order Date" TIMESTAMP PATH '../../dateTime'
) AS U
WHERE P.STATUS = 'NEW'