Converts an XML tree structure to a row set.
Syntax
XMLTABLE ( [ XML_namespace_declaration , ] 'XML_tab_row_pattern' [ XML_query_argument ] [ COLUMNS { column_name FOR ORDINALITY | regular_column_definition } [,...] ] )
You can include only one default namespace declaration item. It can appear in any position in the list of comma-separated namespace declarations or at the end of the list.
- XML_namespace_declaration
-
XMLNAMESPACES ( { 'XML_namespace_URI' AS XML_namespace_prefix | DEFAULT 'XML_namespace_URI' | NO DEFAULT } [,...] )Namespaces declared in the prolog of the queries override any namespace declarations specified here. - XML_query_argument
-
PASSING [ BY VALUE ] { XML_query_context_item | XML_query_variable_spec [,...] } - regular_column_definition
-
column_name_data_type [ PATH 'char_str_literal' ] [ default_clause ]
The optional clauses can be in any order. - XML_query_variable_spec
-
XML_query_variable AS variable_name
- XML_query_variable
-
An XML query variable in the format:
value_expression AS identifier
- 'XML_tab_row_pattern'
- A valid XQuery expression supplied as a character string literal. One row is generated for each item in the result of this query.
- COLUMNS
-
The columns in the table returned by the function.
If not specified, the function returns a single column, named COLUMN_VALUE, which contains XML typed values resulting from the evaluation of the row pattern query.
- column_name
- The name of a column in the output table.
- FOR ORDINALITY
- The column contains an integer that represents a sequence number for the rows generated, starting with 1 for the first row.
- 'XML_namespace_URI'
- The URI (Uniform Resource Identifier) that identifies the XML namespace.
- XML_namespace_prefix
- The namespace prefix.
- BY VALUE
- Row pattern query arguments are passed in by value. This is the default.
- XML_query_content_item
-
A value expression representing an XML query context item.
You can pass in only one context item.
- column_name_data_type
- The data type of a column in the output table.
- PATH 'char_str_literal'
- The XML table column pattern, a query that returns the column value. This query is evaluated relative to the item returned by the row query; that is, it uses the item returned by the row query as the context item. The resulting value is cast to the column data type.
- default_clause
-
The default value for the column if the column query returns no results.
The default clause is not applicable for certain data types such as CLOB, BLOB, and UDTs.