JSON & XML Support

SQL Server has supported XML as a first-class data type since 2005 and gained native JSON functions in SQL Server 2016. There is no separate json data type — JSON is stored in NVARCHAR(MAX) and the engine validates and parses it via dedicated functions. For new development, prefer JSON unless you have specific XML schema/XPath requirements.

Producing JSON — `FOR JSON`

The FOR JSON clause turns any SELECT into a JSON document.

-- AUTO: structure inferred from joins/aliases
SELECT  e.employee_id,
        e.first_name,
        e.last_name,
        d.department_name
FROM    hr.employees   e
JOIN    hr.departments d ON d.department_id = e.department_id
WHERE   e.department_id = 60
FOR JSON AUTO;

-- PATH: full control via dotted aliases for nested objects
SELECT  e.employee_id           AS [id],
        e.first_name            AS [name.first],
        e.last_name             AS [name.last],
        d.department_name       AS [department.name],
        l.city                  AS [department.location.city]
FROM    hr.employees   e
JOIN    hr.departments d ON d.department_id = e.department_id
JOIN    hr.locations   l ON l.location_id   = d.location_id
WHERE   e.employee_id = 100
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES;

Option	Effect
`WITHOUT_ARRAY_WRAPPER`	Emits a single object instead of `[ {...} ]` — useful for one-row results
`INCLUDE_NULL_VALUES`	Keeps `"col": null` instead of omitting nulls
`ROOT('label')`	Wraps everything in `{"label": [...]}`

Consuming JSON — `OPENJSON`, `JSON_VALUE`, `JSON_QUERY`

DECLARE @doc NVARCHAR(MAX) = N'
{
  "order_id": 105,
  "customer": { "id": 1, "name": "Acme Corp" },
  "lines": [
    { "sku": "A-100", "qty": 2, "price": 15.50 },
    { "sku": "B-220", "qty": 1, "price": 42.00 }
  ]
}';

-- Scalar values
SELECT  JSON_VALUE(@doc, '$.order_id')         AS order_id,
        JSON_VALUE(@doc, '$.customer.name')    AS customer_name,
        JSON_QUERY(@doc, '$.customer')         AS customer_obj;

-- Shred an array into rows
SELECT  sku, qty, price
FROM    OPENJSON(@doc, '$.lines')
WITH (
    sku   NVARCHAR(20)  '$.sku',
    qty   INT           '$.qty',
    price DECIMAL(10,2) '$.price'
);

JSON_VALUE returns a scalar (max 4000 chars) — wrap in CAST for typing.
JSON_QUERY returns an object or array as JSON text.
OPENJSON is a table-valued function; the WITH clause is optional but lets you project typed columns.

Validating & Modifying JSON

-- Reject invalid documents at insert time
ALTER TABLE hr.employees
ADD CONSTRAINT chk_meta_json CHECK (ISJSON(profile_meta) = 1);

-- Update a single property in place
UPDATE  hr.employees
SET     profile_meta = JSON_MODIFY(profile_meta, '$.timezone', 'Asia/Kolkata')
WHERE   employee_id = 100;

-- Append to an array (lax mode)
UPDATE  hr.employees
SET     profile_meta = JSON_MODIFY(profile_meta, 'append $.tags', 'on-call')
WHERE   employee_id = 100;

JSON_PATH_EXISTS(@doc, '$.lines[0].sku') (SQL 2022+) returns 1/0 for path existence checks without parsing the value.

XML — `xml` Data Type

DECLARE @x XML = N'
<order id="105">
  <customer id="1">Acme Corp</customer>
  <lines>
    <line sku="A-100" qty="2" price="15.50"/>
    <line sku="B-220" qty="1" price="42.00"/>
  </lines>
</order>';

-- .value(): scalar via XPath, second arg is SQL type
SELECT  @x.value('(/order/@id)[1]',                'INT')          AS order_id,
        @x.value('(/order/customer)[1]',           'NVARCHAR(100)') AS customer;

-- .nodes(): shred to rowset
SELECT  ln.value('@sku',   'NVARCHAR(20)')  AS sku,
        ln.value('@qty',   'INT')           AS qty,
        ln.value('@price', 'DECIMAL(10,2)') AS price
FROM    @x.nodes('/order/lines/line') AS L(ln);

-- .exist(): predicate, returns 1/0
SELECT  @x.exist('/order/lines/line[@qty > 1]') AS has_multi;

-- .query(): returns an XML fragment
SELECT  @x.query('/order/lines/line[@qty > 1]') AS multi_lines;

Producing XML — `FOR XML`

SELECT  e.employee_id, e.first_name, e.last_name
FROM    hr.employees e
WHERE   e.department_id = 60
FOR XML PATH('employee'), ROOT('employees');

FOR XML AUTO infers structure from the query; PATH lets you shape it via column aliases (e.g. [@id], [name/first], [*]); RAW produces a flat element per row.

Indexing XML

-- Primary XML index (required first) — cracks the document into a B-tree
CREATE PRIMARY XML INDEX pxi_emp_meta
    ON hr.employees(profile_xml);

-- Secondary indexes for path / value / property queries
CREATE XML INDEX sxi_emp_meta_path
    ON hr.employees(profile_xml)
    USING XML INDEX pxi_emp_meta FOR PATH;

JSON has no equivalent dedicated index, but you can index a computed column that extracts a JSON property:

ALTER TABLE hr.employees
    ADD timezone AS JSON_VALUE(profile_meta, '$.timezone') PERSISTED;

CREATE NONCLUSTERED INDEX ix_emp_timezone ON hr.employees(timezone);

Best Practices & Pitfalls

Prefer JSON for new work. It's terser, ubiquitous in APIs, and the functions are simpler. XML is heavier but unmatched for schema-validated documents (xml(SCHEMACOLLECTION)).
Don't query JSON for joins repeatedly without indexed computed columns — every JSON_VALUE is a string parse, and that's a recipe for scans.
JSON_VALUE truncates at 4000 chars. For long values use JSON_QUERY or shred via OPENJSON.
Use ISJSON/CHECK constraints to keep junk out of NVARCHAR(MAX) columns acting as JSON.
Avoid storing JSON when relational works. If you query individual properties constantly, normalise into proper columns/tables.
XML namespaces require WITH XMLNAMESPACES(...) at the top of XQuery statements — easy to forget.
For SQL 2022+, prefer JSON_PATH_EXISTS over JSON_VALUE(...) IS NOT NULL for existence checks.

JSON & XML Support

Producing JSON — FOR JSON

Consuming JSON — OPENJSON, JSON_VALUE, JSON_QUERY