SQL XMLTABLE

What is SQL XMLTABLE?

XMLTABLE converts XML data into a relational table by evaluating an XPath expression and projecting the results as rows and columns.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL XMLTABLE: Oracle, PostgreSQL (9.1+), DB2, Teradata, SQL Server (via OPENXML/XQuery but not exact syntax), MySQL and SQLite - not supported

SQL XMLTABLE Full Explanation

XMLTABLE is part of the SQL/XML standard and acts like a table-valued function. It takes an XML value (or XML document) and an XPath/XQuery expression, produces a row for each matching node, and maps XML elements or attributes to relational columns with explicit data types. Because the XML parsing happens inside the database engine, XMLTABLE lets you join or filter XML content with regular SQL, eliminating the need for external parsing. It supports namespace declarations, default values, column-level PATH overrides, and optional ORDINALITY to generate a sequence number per row. Performance depends on XML indexes and the database’s XML storage model. Not all databases implement every feature, so check dialect specifics.

SQL XMLTABLE Syntax

XMLTABLE (
  [ XMLNAMESPACES ( 'uri' AS prefix, ... ) , ]
  'row_xpath' PASSING xml_value [ , xml_value2 AS alias2 ]
  COLUMNS
    column_name1 data_type1 [ PATH 'col_xpath1' ] [ DEFAULT default_expr1 ] [ NOT NULL ],
    column_name2 data_type2 [ PATH 'col_xpath2' ] ...
    [ , column_nameN data_typeN ... ]
);

SQL XMLTABLE Parameters

XMLNAMESPACES (clause) - Declares XML namespaces used in XPath expressions
row_xpath (string) - XPath/XQuery that selects the set of XML nodes to turn into rows
PASSING (keyword) - Supplies one or more XML values to the XPath context
xml_value (XML) - The XML document or fragment being queried
COLUMNS (clause) - Defines the relational projection for each row
column_name (identifier) - Name of the output column
data_type (SQL type) - Data type to cast the XML value into (VARCHAR, NUMBER, DATE, etc.)
PATH (string) - Optional XPath for the column, relative to the row node
DEFAULT (expression) - Fallback value when the XPath result is empty
ORDINALITY (keyword) - Adds an auto-incrementing column indicating row position

Example Queries Using SQL XMLTABLE

-- Oracle: extract books from an XML document stored in a table column
SELECT b.id,
       x.title,
       x.author,
       x.price
FROM   books_xml b,
       XMLTABLE(
         '/catalog/book'
         PASSING b.xml_doc
         COLUMNS 
           title  VARCHAR2(100) PATH 'title',
           author VARCHAR2(100) PATH 'author',
           price  NUMBER        PATH 'price'
       ) x;

-- PostgreSQL: namespace example with ordinality
WITH data(xmlcol) AS (
  VALUES (
    '<ns:orders xmlns:ns="http://ex.com/ns"><ns:order id="A1"/><ns:order id="B2"/></ns:orders>'::xml)
)
SELECT o.idx,
       o.order_id
FROM data d,
LATERAL XMLTABLE(
  XMLNAMESPACES('http://ex.com/ns' AS ns),
  '/ns:orders/ns:order' PASSING d.xmlcol
  COLUMNS 
    order_id TEXT  PATH '@id',
    idx      FOR ORDINALITY
) AS o;

Expected Output Using SQL XMLTABLE

Each matching XML node becomes a row
The defined COLUMNS appear as regular relational columns you can join, filter, and aggregate
If no node matches, zero rows are returned

Use Cases with SQL XMLTABLE

Shredding XML messages (e.g., SOAP, configuration files) into relational form for analytics
Joining XML payloads stored in a column with master tables
Reporting on XML-formatted logs without exporting them
Migrating legacy XML data to structured tables

Common Mistakes with SQL XMLTABLE

Forgetting to declare namespaces, causing XPath expressions to return no rows
Using incorrect PATH expressions relative to the row context
Casting XML text to an incompatible SQL data type
Expecting XMLTABLE to update the underlying XML (it is read-only)
Omitting ORDINALITY when row order matters

First Introduced In

SQL:2006 (SQL/XML) and Oracle Database 10g Release 2

Frequently Asked Questions

What databases support XMLTABLE?

Oracle, PostgreSQL, DB2, Teradata, and some columnar engines support it. MySQL and SQLite do not.

Do I need to create a table to use XMLTABLE?

No. XMLTABLE is used inline in the FROM clause and behaves like a derived table; no permanent table is created.

How do I return the position of each XML element?

Add a column with the FOR ORDINALITY keyword. The column will contain a sequential integer starting at 1.

Can XMLTABLE handle large XML documents?

Yes, but performance relies on XML indexes and streaming capabilities. Always index the XML column or store pre-shredded data for very large documents.