XMLTABLE

The XMLTABLE function uses XQuery to produce tabular output. The XMLTABLE function is implicitly a nested table and it can be used within FROM clauses. XMLTABLE is part of the SQL/XML 2006 specification.

Usage
XMLTABLE([<NSP>,] xquery-expression [<PASSING>] [COLUMNS <COLUMN>, ... ]) AS name
COLUMN := name (FOR ORDINALITY | (datatype [DEFAULT expression] [PATH string]))

For the definition of NSP - XMLNAMESPACES, see XMLELEMENT in XML functions. For the definition of PASSING, see XMLQUERY in XML functions.

Note
 See also XQuery optimization.
Parameters

  • The optional XMLNAMESPACES clause specifies the namepaces that you can use in the XQuery and COLUMN path expressions.

  • The xquery-expression must be a valid XQuery. Each sequence item returned by the xquery is used to create a row of values as defined by the COLUMNS clause.

  • If COLUMNS is not specified, that is equivalent to a COLUMNS clause that returns the entire item as an XML value, as in the following example:

    "COLUMNS OBJECT_VALUE XML PATH '."'

  • FOR ORDINALITY columns are typed as integers and return 1-based item numbers as their value.

  • Each non-ordinality column specifies a type, and optionally specifies a PATH and a DEFAULT expression.

  • If PATH is not specified, then the path is the same as the column name.

Syntax Rules

  • You can specify only 1 FOR ORDINALITY column.

  • Columns names must not contain duplicates.

  • You can use binary large object (BLOB) datatypes, but there is built-in compatibility only for xs:hexBinary values. For xs:base64Binary, use a workaround of a PATH that uses the following explicit value constructor: xs:base64Binary(<path>).

  • The column expression must evaluate to a single value if a non-array type is expected.

  • If an array type is specified, then an array is returned, unless there are no elements in the sequence, in which case a null value is returned.

  • An empty element is not a valid null value, because its value is effectively an empty string. Use the xsi:nil attribute to specify a null value for an element.

XMLTABLE examples
Use of PASSING, returns 1 row [1]
select * from xmltable('/a' PASSING xmlparse(document '<a id="1"/>') COLUMNS id integer PATH '@id') x
As a nested table
select x.* from t, xmltable('/x/y' PASSING t.doc COLUMNS first string, second FOR ORDINALITY) x
Invalid multi-value
select * from xmltable('/a' PASSING xmlparse(document '<a><b id="1"/><b id="2"/></a>') COLUMNS id integer PATH 'b/@id') x
Array multi-value
select * from xmltable('/a' PASSING xmlparse(document '<a><b id="1"/><b id="2"/></a>') COLUMNS id integer[] PATH 'b/@id') x
Nil element
select * from xmltable('/a' PASSING xmlparse(document '<a xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><b xsi:nil="true"/></a>') COLUMNS id integer PATH 'b') x
Note
 In the preceding example, an exception would be thrown if the nil attribute (xsi:nil="true") were not specified, converting b to an integer value.

results matching ""

    No results matching ""