XMLTABLE

The XMLTABLE function returns a result table from the evaluation of XPath expressions, possibly using specified input arguments as XPath variables. Each item in the result sequence of the row XPath expression represents a row of the result table.

row-xquery-argument

xml-table-regular-column-definition

xml-table-ordinality-column-definition

Notes:

¹ xquery-context-item-expression must not be specified more than one time.
² The xml-table-ordinality-column-definition clause must not be specified more than one time.
³ Neither the default-clause nor the PATH clause can be specified more than one time.

data-type

ccsid-clause

normalize-clause

default-clause

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 XPath 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 XPath prolog within an XPath expression can override these namespaces.

If xmlnamespaces-declaration is not specified, only the pre-established set of statically known namespaces apply to the XPath expressions.

row-xquery-expression-constant

Specifies an SQL string constant that is interpreted as an XPath expression using supported XPath language syntax. 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 XPath 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 be an empty string or a string of all blanks.

PASSING

Specifies input values and the manner in which these values are passed to the XPath expression specified by row-xquery-expression-constant.

BY VALUE

Specifies that any XML arguments are passed by value. When XML values are passed by value, the XPath evaluation uses a copy of the XML data. This is the default behavior. Db2® for i binds the XPath variable expression to a document node that represents the XML input value.

This clause has no impact on how non-XML values are passed. Non-XML values always create a copy of the value during the cast to XML.

row-xquery-argument

Specifies an argument that is to be passed to the XPath expression specified by row-xquery-expression-constant. row-xquery-argument specifies an SQL expression that is evaluated before being passed to the XPath expression.

If the data type of row-xquery-argument is not XML, the result of the expression is converted to XML. For xquery-variable-expression, a null value is converted to an XML empty sequence.

How row-xquery-argument is used in the XPath expression depends on whether the argument is specified as an xquery-context-item-expression or an xquery-variable-expression.

row-xquery-argument must not contain a NEXT VALUE or PREVIOUS VALUE expression or an OLAP specification.

xquery-context-item-expression: Specifies an SQL expression that returns a value that is XML or that is a type that has a supported conversion to XML.
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 after being converted 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 XPath expression specified by row-xquery-expression-constant during execution. The expression must return a value that is XML or that is a type that has a supported conversion to XML.
xquery-variable-expression specifies an argument that will be passed to row-xquery-expression-constant as an XPath variable. If xquery-variable-expression is the null value, the XPath variable is set to an XML empty sequence. The scope of the XPath variables that are created from the PASSING clause is the XPath 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 XPath variable. The identifier is a name that must be in the form of an XML NCName. See the W3C XML namespace specifications for more details on valid names. The leading dollar sign ($) that precedes variable names in the XPath language must not be included as part of identifier. The identifier must not be greater than 128 bytes in length. Two arguments within the same PASSING clause cannot use the same identifier.
BY VALUE: Specifies that xquery-variable-expression is passed by value. When XML values are passed by value, the XPath evaluation uses a copy of the XML data. Db2 for i binds the XPath variable expression to a document node that represents the XML input value. If BY VALUE is not specified following xquery-variable-expression, XML arguments are passed using the default passing mechanism that is provided through the syntax that follows the PASSING keyword.; This clause is only valid for input values with the XML data type. Non-XML values always create a copy of the value during the cast to XML.

Table 1. Supported SQL to XML conversions
SQL type	XML type	Notes
SMALLINT	xs:integer
INTEGER	xs:integer
BIGINT	xs:integer
DECIMAL NUMERIC	xs:decimal	Decimal numbers with a precision greater than 34 can lose precision during processing.
FLOAT DOUBLE DECFLOAT	xs:double
CHAR VARCHAR CLOB GRAPHIC VARGRAPHIC DBCLOB	xs:string	When character string values are cast to XML values, the resulting xs:string atomic value cannot contain illegal XML characters. If the input character string is not in Unicode, the input characters are converted to Unicode. CHAR and VARCHAR strings cannot have a CCSID of 65535 or be defined for bit data.
DATE	xs:date	The xs:date value will not have a timezone component. For comparisons, the timezone is implicitly assumed to be UTC. If needed, the fn:adjust-timezone() function can be used to explicitly set the timezone.
TIME	xs:time	The xs:time value will not have a timezone component. For comparisons, the timezone is implicitly assumed to be UTC. If needed, the fn:adjust-timezone() function can be used to explicitly set the timezone.
TIMESTAMP	xs:dateTime	The xs:dateTime value will not have a timezone component. For comparisons, the timezone is implicitly assumed to be UTC. If needed, the fn:adjust-timezone() function can be used to explicitly set the timezone.

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 type XML is returned with the value based on the sequence item from evaluating the XPath expression in the row-xquery-expression (equivalent to specifying PATH '.'). To reference this result column, a column-name must be specified in the correlation-clause following the table function.

The sum of all the result column lengths cannot exceed 64K bytes. For information on the byte counts of columns according to data type, see Maximum row sizes. Assume the number of row-xquery-arguments is N. Start of change There must be no more than 8000-N columns. End of change

xml-table-regular-column-definition

Specifies one output column of the result table including the column name, data type, and an XPath 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 result table.

data-type

Specifies the data type of the column. For CHAR and VARCHAR columns, the CCSID cannot be 65535.

BY VALUE

Specifies that the result column is returned by value. When XML values are returned by value, a copy of the XML data is returned. This is the default behavior. Db2 for i constructs a document node for the XML result when the value is returned from the table function. This clause must not be specified for a column with a data type that is not XML.

default-clause

Specifies a default value for the column. For XMLTABLE result columns, the default is applied when the processing of the XPath expression contained in column-xquery-expression-constant returns an empty sequence.

PATH column-xquery-expression-constant

Specifies a string constant that is interpreted as an XPath expression using supported XPath language syntax. The column-xquery-expression-constant specifies an XPath expression that determines the column value with respect to an item that is the result of evaluating the XPath expression in row-xquery-expression-constant. Given an item from the result of processing the row-query-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 an empty sequence is returned, the default-clause provides the value of the column.
If an empty sequence is returned and no default-clause was specified, the null value is assigned to the column.
If a non-empty sequence is returned, the value is converted to the data-type specified for the column. An error could be returned from processing this implicit conversion.

The value for column-xquery-expression-constant must not be an empty string or a string of all blanks. If this clause is not specified, the default XPath expression is the column-name.

xml-table-ordinality-column-definition

Specifies the ordinality column of the result table.

colum-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 result 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 XPath expression in row-xquery-expression-constant.

Table 2. Supported XML to SQL result column conversions
XML type	SQL type	Notes
xs:integer	SMALLINT INTEGER BIGINT
xs:decimal	DECIMAL NUMERIC	The resulting xs:decimal value is converted, if necessary, to the precision and scale of the target data type. The necessary number of leading zeros is added or removed. In the fractional part of the number, the necessary number of trailing zeros is added or the necessary number of digits is eliminated. This truncation behavior is similar to the behavior of the cast from DECIMAL to DECIMAL. Decimal numbers with a precision greater than 34 can lose precision during processing.
xs:double	FLOAT DOUBLE REAL DECFLOAT	If the target type is FLOAT, DOUBLE, or REAL and the source XML value after the XPath cast is an xs:double value of INF, -INF, or NaN, an error is returned. If the source value is an xs:double negative zero, the value is converted to positive zero. If the source value is beyond the range of the target data type, an overflow error is returned. If the source value contains more significant digits than the precision of the target data type, the source value is rounded to the precision of the target data type. If the target type is DECFLOAT and the source XML value is an xs:double value of INF, -INF, or NaN, the result will be the corresponding special DECFLOAT values INF, -INF, or NaN. If the source value is an xs:double negative zero, the result is negative zero. If the target type is DECFLOAT(16) and the source value is beyond the range of DECFLOAT(16), an overflow error is returned. If the source value has more than 16 significant digits, the value is rounded according to the ROUNDING mode that is in effect. This rounding behavior is the same as what is used during the cast of DECFLOAT(34) to DECFLOAT(16).
xs:string	CHAR VARCHAR CLOB GRAPHIC VARGRAPHIC DBCLOB	The resulting XML value is converted, if necessary, to the CCSID of the target data type using the rules described in Conversion rules for assignments before it is converted to the target type with a limited length. Truncation occurs if the specified length limit is smaller than the length of the resulting string after CCSID conversion. A warning occurs if any non-blank characters are truncated. If the target type is a fixed-length string type (CHAR or GRAPHIC) and the specified length of the target type is greater than the length of the resulting string from CCSID conversion, blanks are padded at the end. This truncation and padding behavior is similar to retrieval assignment of character or graphic strings.
xs:date	DATE	The resulting XML value is adjusted to UTC time and the time zone component is removed. The year part of the resulting xs:date value must be in the range of 0001 to 9999.
xs:time	TIME	The resulting XML value is adjusted to UTC time and the time zone component is removed. Any fractional seconds are truncated from the result.
xs:dateTime	TIMESTAMP	The resulting XML value is adjusted to UTC time and the time zone component is removed. The year part of the resulting xs:dateTime value must be in the range of 0001 to 9999. If the target timestamp type has a precision less than 12, the fractional seconds part of the xs:dateTime value is truncated to the target timestamp precision.

The result of the function is a table. If the evaluation of any of the XPath expressions results in an error, then the XMLTABLE function returns the XPath error.

Example

List as a table result the purchase order items for orders with a status of 'Unshipped'.

SELECT U."PO ID", U."Part #", U."Product Name",
       U."Quantity", U."Price", U."Order Date"
  FROM PURCHASEORDER P,
       XMLTABLE('$po/PurchaseOrder/itemlist/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'