Guide to SQL/XML: Using XMLQUERY and XMLTABLE

The SQL/XML standard defines how relational database management systems seamlessly store, manipulate, and query XML data alongside structured relational data. This article provides a straightforward overview of the SQL/XML specification and explains the operational mechanics of two of its most essential functions: XMLQUERY and XMLTABLE.

What is the SQL/XML Standard?

SQL/XML is a formal extension of the ANSI/ISO SQL standard (specifically ISO/IEC 9075-14). It introduces native support for hierarchical XML data within traditional relational database management systems (RDBMS) such as Oracle, IBM Db2, and PostgreSQL.

The standard specifies: - A native XML data type to store well-formed XML documents or fragments. - Publishing functions (such as XMLELEMENT, XMLFOREST, and XMLAGG) to generate XML documents from relational data. - Query and transformation functions (such as XMLQUERY, XMLTABLE, XMLEXISTS, and XMLCAST) that integrate XQuery and XPath expressions into SQL statements.

How XMLQUERY Operates

XMLQUERY evaluates an XQuery or XPath expression against an XML document and returns the result as an XML data type instance. It is primarily used when you need to extract specific XML nodes, fragments, or scalar values directly without transforming the output into relational rows and columns.

Syntax Structure

XMLQUERY(
    'xquery_expression'
    PASSING context_item [AS identifier]
    [RETURNING CONTENT | RETURNING SEQUENCE]
)

Operational Workflow

1. Context Binding: The PASSING clause supplies the source XML (from an XML column, variable, or literal) to the XQuery engine as the evaluation context.
2. Path Evaluation: The database applies the XQuery/XPath expression to the input document to locate matching elements or attributes.
3. Result Generation: The matched nodes or computed values are returned as an XML data type fragment.

Example

SELECT XMLQUERY(
    '/order/customer/name' 
    PASSING order_xml 
    RETURNING CONTENT
) AS customer_name
FROM orders;

If order_xml contains <order><customer><name>Alice</name></customer></order>, the query returns <name>Alice</name>.

How XMLTABLE Operates

XMLTABLE decomposes hierarchical XML structures into a relational format consisting of rows and columns. It functions as an inline table-valued function, allowing you to join XML nodes directly with standard SQL tables, apply WHERE filters, or perform standard aggregations.

Syntax Structure

XMLTABLE(
    'row_xquery_expression'
    PASSING context_item
    COLUMNS
        column_name data_type PATH 'column_xpath_expression',
        ...
)

Operational Workflow

1. Row Generation: The primary XQuery expression evaluates against the PASSING XML context to generate a sequence of nodes. Each node in the resulting sequence represents one row in the output table.
2. Column Extraction: For every generated row node, the sub-expressions defined in the COLUMNS clause execute.
3. Data Type Casting: The extracted values from each column’s PATH are automatically cast into the specified relational data types (e.g., VARCHAR, INTEGER, DECIMAL).

Example

Given an XML structure containing multiple items:

<order id="101">
  <items>
    <item id="1"><name>Keyboard</name><price>49.99</price></item>
    <item id="2"><name>Mouse</name><price>19.99</price></item>
  </items>
</order>

The XMLTABLE function flattens the items into relational rows:

SELECT x.item_id, x.item_name, x.price
FROM orders o,
XMLTABLE(
    '/order/items/item'
    PASSING o.order_xml
    COLUMNS
        item_id   INT          PATH '@id',
        item_name VARCHAR(100) PATH 'name',
        price     DECIMAL(8,2) PATH 'price'
) x;

Result:

XMLQUERY vs. XMLTABLE: When to Use Which