SQL TL;DR: JSON and XML Functions

JSON & XML Functions

/tldr: Interacting with semi-structured data directly in SQL.

Semi-Structured Data Querying Data Transformation

THE CORE IDEA: SQL Meets Document Data

These functions allow you to treat JSON and XML data stored in a text column (like VARCHAR or NVARCHAR) as if it were relational data. This eliminates the need for applications to perform complex parsing logic.

1. Essential JSON Functions

JSON functions use path expressions (similar to XPath) to navigate the JSON structure.

JSON_VALUE(expression, path)

**Purpose:** Extracts a **scalar value** (e.g., number, string, boolean) from a JSON string.
**Result:** Returns the value as a single SQL data type.

DECLARE @json_doc NVARCHAR(MAX) = '{"name": "Alice", "city": "London", "is_admin": true}';

-- Returns 'London' as a string
SELECT JSON_VALUE(@json_doc, '$.city');

JSON_QUERY(expression, path)

**Purpose:** Extracts an **object or array** from a JSON string.
**Result:** Returns the JSON fragment as a valid JSON string (usually in NVARCHAR).

DECLARE @json_doc NVARCHAR(MAX) = '{"user": {"id": 101, "role": "dev"}, "items": [1, 2, 3]}';

-- Returns '{"id": 101, "role": "dev"}' as JSON
SELECT JSON_QUERY(@json_doc, '$.user');

ISJSON(expression)

**Purpose:** Checks if a string is valid JSON.
**Result:** Returns 1 if valid, 0 if invalid, and NULL if the expression is NULL.

-- Checks validity (Returns 1)
SELECT ISJSON('{"product": "keyboard"}');

OPENJSON(expression)

**Purpose:** Parses a JSON array or object and returns the properties and values as **rows and columns** (a temporary table).
**Usage:** Crucial for reading and loading data from JSON into relational tables.

DECLARE @json_array NVARCHAR(MAX) = '[{"id": 1, "sku": "A"}, {"id": 2, "sku": "B"}]';

-- Flattens the array into a relational structure
SELECT *
FROM OPENJSON(@json_array)
WITH (
    ProductID INT '$.id', 
    SKU VARCHAR(10) '$.sku'
);

2. Essential XML Functions (XPath)

XML functions rely on **XPath** to query nodes and values within the XML structure. Many databases use a dedicated XML data type.

.value(XPath, SQLType)

**Purpose:** Extracts a **scalar value** from the XML and converts it to a specified SQL data type.
**Note:** This is a method called directly on the XML data type.

DECLARE @xml_doc XML = '<user id="100"><name>Bob</name></user>';

-- Extracts the content of the <name> node as VARCHAR
SELECT @xml_doc.value('(/user/name)[1]', 'VARCHAR(50)');

.query(XPath)

**Purpose:** Extracts an **XML fragment** (a node, array of nodes, or object) and returns it as a new XML data type.

DECLARE @xml_doc XML = '<products><prod id="1"/><prod id="2"/></products>';

-- Returns the fragment: <prod id="1"/>
SELECT @xml_doc.query('/products/prod[1]');

.nodes(XPath)

**Purpose:** Used for shredding XML. Returns a rowset where each row represents a selected XML node.
**Usage:** Similar to OPENJSON, it's used to pivot the structure into relational rows/columns.

-- Flattens all <item> nodes in the document
SELECT 
    T.item.value('./@name', 'VARCHAR(50)') AS ItemName
FROM 
    YourTable
CROSS APPLY 
    YourXMLColumn.nodes('/purchase_order/item') AS T(item);

Use JSON functions for simple key-value lookups; use OPENJSON/nodes for complex data loading.