17.10 - XMLTABLE Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - XML Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1140-171K
Language
English (United States)
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.

Syntax Elements

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.