Skip to main content
Skip table of contents

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]))

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;;

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.