You can specify at most one
XMLTable maps the result of an XQuery evaluation into relational rows and columns. You can query the result returned by the function as a virtual relational table using SQL.
XMLNAMESPACESclause contains a set of XML namespace declarations. These declarations are referenced by the XQuery expression (the evaluated
XQuery_string), which computes the row, and by the XPath expression in the
XML_table_column, which computes the columns for the entire
XMLTablefunction. If you want to use qualified names in the
PATHexpressions of the
COLUMNSclause, then you need to specify the
XQuery_stringis a literal string. It is a complete XQuery expression and can include prolog declarations. The value of XQuery_string serves as input to the XMLTable function; it is this XQuery result that is decomposed and stored as relational data.
XML_passing_clauseis an expression returning an
XMLTypeor an instance of a SQL scalar data type that is used as the context for evaluating the XQuery expression. You can specify only one
PASSINGclause without an identifier. The result of evaluating each
expris bound to the corresponding identifier in the
XQuery_string. If any
exprthat is not followed by an
ASclause, then the result of evaluating that expression is used as the context item for evaluating the
XQuery_string. This clause supports only passing by value, not passing by reference. Therefore, the
VALUEkeywords are optional and are provided for semantic clarity.
REFclause causes the result of the XQuery evaluation to be returned by reference. This allows you to refer to any part of the source data in the
If you omit this clause, then the result of the XQuery evaluation is returned by value. That is, a copy of the targeted nodes is returned instead of a reference to the actual nodes. In this case, you cannot refer to any data that is not in the returned copy in the
XML_table_columnclause. In particular, you cannot refer to data that precedes the targeted nodes in the source data.
COLUMNSclause defines the columns of the virtual table to be created by
If you omit the
XMLTablereturns a row with a single
columnis to be a column of generated row numbers. There must be at most one
ORDINALITYclause. It is created as a
For each resulting column except the
ORDINALITYcolumn, you must specify the column data type, which can be
XMLTypeor any other data type.
If the column data type is
XMLType, then specify the
XMLTYPEclause. If you specify the optional
REFclause, then a reference to the source data targeted by the
PATHexpression is returned as the column content. Otherwise,
columncontains a copy of that targeted data.
XMLTypedata by reference lets you specify other columns whose paths target nodes in the source data that are outside those targeted by the
PATHexpression for column.
If the column data type is any other data type, then specify
PATHclause specifies that the portion of the XQuery result that is addressed by XQuery expression string is to be used as the column content.
If you omit
PATH, then the XQuery expression
columnis assumed. For example:
XMLTable(... COLUMNS xyz)
is equivalent to
XMLTable(... COLUMNS xyz PATH 'XYZ')
You can use different
PATHclauses to split the XQuery result into different virtual-table columns.
DEFAULTclause specifies the value to use when the
PATHexpression results in an empty sequence. Its
expris an XQuery expression that is evaluated to produce the default value.
Oracle XML DB Developer's Guide for more information on the
XMLTablefunction, including additional examples, and on XQuery in general
Appendix C in Oracle Database Globalization Support Guide for the collation derivation rules, which define the collation assigned to each character data type column in the table generated by
The following example converts the result of applying the XQuery
'/Warehouse' to each value in the
warehouse_spec column of the
warehouses table into a virtual relational table with columns
SELECT warehouse_name warehouse, warehouse2."Water", warehouse2."Rail" FROM warehouses, XMLTABLE('/Warehouse' PASSING warehouses.warehouse_spec COLUMNS "Water" varchar2(6) PATH 'WaterAccess', "Rail" varchar2(6) PATH 'RailAccess') warehouse2; WAREHOUSE Water Rail ----------------------------------- ------ ------ Southlake, Texas Y N San Francisco Y N New Jersey N N Seattle, Washington N Y