JSON and XML Functions

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

This page documents the standard SQL functions for working with JSON (introduced by SQL:2016 and extended in SQL:2023, part of ISO/IEC 9075-2 (SQL/Foundation)) and XML (defined by the SQL/XML, or ISO/IEC 9075-14 (SQL/XML), standard). Only the ISO-standard function names and syntax are covered here.

JSON Functions

The JSON functions let you extract scalar values, extract JSON fragments, tabulate JSON data into rows and columns, and test for the existence of a value at a given path, all addressed using SQL/JSON path expressions (the $ syntax).

Actual DBMS support for the standard JSON functions varies significantly. Some database products only partially implement SQL:2016, some support only a subset of these functions, and others predate the standard entirely. Verify the exact syntax, path-expression dialect, and behavior against your own DBMS’s documentation before relying on any of these functions.

JSON_VALUE

Extracts a single scalar value (a string, number, or boolean) from a JSON document at the given path.

Parameters

json_document — the JSON value or column to query.
path — an SQL/JSON path expression (e.g. $.name) identifying the scalar to extract.
RETURNING type (optional) — the SQL data type the extracted value should be cast to.
ON EMPTY / ON ERROR clauses (optional) — specify the behavior (NULL, ERROR, or a DEFAULT value) when the path finds nothing or evaluation fails.

SELECT JSON_VALUE(
    '{"id": 42, "name": "Widget", "price": 19.99}',
    '$.name'
) AS product_name;

JSON_QUERY

Extracts a JSON object or array (rather than a scalar) from a JSON document at the given path.

Parameters

json_document — the JSON value or column to query.
path — an SQL/JSON path expression (e.g. $.items) identifying the object or array to extract.
WITH WRAPPER / WITHOUT WRAPPER (optional) — controls whether multiple matched values are wrapped in a JSON array.
ON EMPTY / ON ERROR clauses (optional) — specify the behavior when the path finds nothing or evaluation fails.

SELECT JSON_QUERY(
    '{"id": 42, "items": [{"sku": "A1"}, {"sku": "B2"}]}',
    '$.items'
) AS items_json;

JSON_TABLE

Transforms a JSON document into a relational result set (a set of rows and columns), suitable for use in a FROM clause like any other table.

Parameters

json_document — the JSON value or column to convert into rows.
path — an SQL/JSON path expression identifying the array of elements to turn into rows.
COLUMNS (…) — a list of column definitions, each naming an output column, its SQL data type, and the nested path expression used to populate it from each JSON element.

SELECT t.sku, t.quantity
FROM JSON_TABLE(
    '{"items": [{"sku": "A1", "quantity": 3}, {"sku": "B2", "quantity": 1}]}',
    '$.items[*]'
    COLUMNS (
        sku VARCHAR(10) PATH '$.sku',
        quantity INT PATH '$.quantity'
    )
) AS t;

JSON_EXISTS

Tests whether a given SQL/JSON path expression matches at least one value within a JSON document, returning a boolean.

Parameters

json_document — the JSON value or column to test.
path — an SQL/JSON path expression to evaluate against the document.
ON ERROR clause (optional) — specifies the result (TRUE, FALSE, or ERROR) to return when path evaluation fails.

SELECT JSON_EXISTS(
    '{"id": 42, "items": [{"sku": "A1"}]}',
    '$.items[0].sku'
) AS has_first_sku;

XML Functions

The XML functions let you construct XML elements from SQL values, query and shred XML documents using XQuery expressions, and tabulate XML content into rows and columns.

Actual DBMS support for the standard SQL/XML functions varies significantly. Some database products only partially implement the SQL/XML standard, some support only a subset of these functions, and others predate the standard entirely. Verify the exact syntax and behavior against your own DBMS’s documentation before relying on any of these functions.

XMLELEMENT

Constructs an XML element from a name and a list of attributes and/or content values.

Parameters

NAME element_name — the tag name of the XML element to construct.
XMLATTRIBUTES (…) (optional) — a list of value AS attribute_name pairs to render as XML attributes.
content — one or more expressions (scalar values or nested XML) to render as the element’s content.

SELECT XMLELEMENT(
    NAME "product",
    XMLATTRIBUTES(p.id AS "id"),
    p.name
) AS product_xml
FROM products p;

XMLFOREST

Constructs a sequence of XML elements, one per given value, using each value’s column name (or alias) as the element’s tag name.

Parameters

value AS element_name — one or more expressions, each paired with the tag name to use for its generated element (the column name is used if no alias is given).

SELECT XMLELEMENT(
    NAME "product",
    XMLFOREST(p.id AS "id", p.name AS "name", p.price AS "price")
) AS product_xml
FROM products p;

XMLQUERY

Evaluates an XQuery expression against an XML value and returns the resulting XML sequence.

Parameters

xquery_expression — the XQuery expression to evaluate.
PASSING context — binds a SQL value (e.g. an XML column) to the context item (or a named variable) used within the XQuery expression.
RETURNING CONTENT / RETURNING SEQUENCE (optional) — specifies the shape of the returned result.

SELECT XMLQUERY(
    '/product/name/text()'
    PASSING p.product_xml
    RETURNING CONTENT
) AS product_name
FROM products p;

XMLTABLE

Transforms XML content into a relational result set (a set of rows and columns), suitable for use in a FROM clause like any other table.

Parameters

xquery_expression — an XQuery expression identifying the sequence of XML nodes to turn into rows.
PASSING context — binds a SQL value (e.g. an XML column) to the context item used to evaluate the expression.
COLUMNS (…) — a list of column definitions, each naming an output column, its SQL data type, and the nested XQuery/XPath expression used to populate it from each XML node.

SELECT t.sku, t.quantity
FROM XMLTABLE(
    '/items/item'
    PASSING XMLPARSE(DOCUMENT '<items><item><sku>A1</sku><quantity>3</quantity></item></items>')
    COLUMNS
        sku VARCHAR(10) PATH 'sku',
        quantity INT PATH 'quantity'
) AS t;