XMLTABLE

You are looking at an older version of the documentation. The latest version is found here.

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 Data 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()&amp;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;;