XMLTABLE

The XMLTABLE function uses XQuery to produce tabular output. The XMLTABLE function is implicitly a nested table and may be correlated to the preceding FROM clause entries. XMLTABLE is part of the SQL/XML 2006 specification.

Usage

SQL

XMLTABLE([<NSP>,] xquery-expression [<PASSING>] [COLUMNS <COLUMN>, ... )] AS name

SQL

COLUMN := name (FOR ORDINALITY | (datatype [DEFAULT expression] [PATH string]))

See XMLELEMENT for the definition of NSP - XMLNAMESPACES;
See XMLQUERY for the definition of PASSING;
See also XMLQUERY.

Parameters

The optional XMLNAMESPACES clause specifies the namespaces for use in the XQuery and COLUMN path expressions;
The XQuery-expression should be a valid XQuery. Each sequence item returned by the XQuery will be used to create a row of values as defined by the COLUMNS clause;
If COLUMNS is not specified, then that is the same as having the COLUMNS clause: COLUMNS OBJECT_VALUE XML PATH '.', which returns the entire item as an XML value;
A FOR ORDINALITY column is entered as an integer and will return the 1-based item number as its value;
Each non-ordinality column specifies a type and optionally a PATH and a DEFAULT expression;
If PATH is not specified, the path will be the same as the column name.

Syntax Rules

Only one A FOR ORDINALITY column may be specified;
Column names must contain no duplicates.

Examples

1. Use of PASSING returns 1 row [1]:

SQL

SELECT * FROM XMLTABLE('/a' PASSING XMLPARSE(DOCUMENT '<a id="1"/>') COLUMNS id INTEGER PATH '@id') x;;

2. As a nested table:

SQL

SELECT x.* FROM t, XMLTABLE('/x/y' PASSING t.doc COLUMNS first STRING, second FOR ORDINALITY) x;;

Array Support

The CData Virtuality Server supports arrays in XMLTABLE. This enables you to load any amount of serial data, which can be interpreted as an array:

SQL

SELECT 
"xml_table.idColumn",
"xml_table.id",
"xml_table.key",
"xml_table.fixVersions"
FROM (
    CALL "jira".invokeHTTP(
        action => 'GET',
        requestContentType => 'application/json',
        endpoint => 'https://jira.your-domain.com/rest/api/latest/search?jql=sprint%20in%20openSprints()&#38;expands=issues'
    )
) w,
XMLTABLE(XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as "xsi"), '/root/issues' PASSING JSONTOXML('root', to_chars(w.result,'UTF-8'))
    COLUMNS 
    "idColumn" FOR ORDINALITY,
    "id" STRING  PATH 'id',
    "key" STRING  PATH 'key',
    "fixVersions" STRING[] PATH 'fields/fixVersions/name'
) "xml_table";;

Null Input

When being passed a null value as input, XMLTABLE returns an empty result:

SQL

SELECT *
FROM XMLTABLE(
	'/root'
   PASSING CAST(NULL AS XML)
   COLUMNS i INTEGER
) x;;