Day 27: JSON & XML in T-SQL

A. Lesson overview

Activity breakdown

Business scenario. The new web shop sends order events as JSON; two suppliers send price catalogues as XML. The data team must load both into relational tables, check them against the product catalogue, and return order summaries as JSON to the web team.

B. Theory explained clearly

Part 1 — Primer: two document formats

JSON (JavaScript Object Notation):

{
  "orderRef": "WEB-5001",                         ← string
  "customer": { "id": 1, "email": "…" },          ← object (name/value pairs in { })
  "lines": [ { "sku": "TNT-002", "qty": 1 } ],    ← array (ordered values in [ ])
  "tags": [ "gift" ],
  "paid": true,                                   ← true/false
  "note": null                                    ← null
}

A path addresses part of a document: $ is the whole document, $.customer.email a nested property, $.lines[0] the first array element (zero-based), $.lines[0].sku a property inside it.

XML (eXtensible Markup Language) uses elements and attributes:

<catalogue supplier="Northern Outdoor Supply" currency="GBP">   ← element with attributes
  <item sku="TNT-001"><cost>92.50</cost></item>                 ← nested elements
</catalogue>

XML is addressed with XPath/XQuery: /catalogue/item (elements), /catalogue/@currency (an attribute), (/catalogue/item/cost)[1] (the first match).

In SQL Server, JSON is stored as text (nvarchar) — or, in SQL Server 2025, in the native json type; XML has its own xml data type.

Part 2 — Reading JSON

OPENJSON without a schema returns key, value, type (0 null, 1 string, 2 number, 3 true/false, 4 array, 5 object). With a WITH clause, you define typed columns and their paths:

SELECT e.EventID, l.Sku, l.Qty, l.Price
FROM   Lab.OrderEvent AS e
CROSS APPLY OPENJSON(e.Payload, '$.lines')
       WITH (Sku   varchar(20)   '$.sku',
             Qty   int           '$.qty',
             Price decimal(10,2) '$.price') AS l;

Paths are lax by default: a missing property returns NULL. Prefix with strict ('strict $.orderRef') to raise an error instead. JSON_VALUE returns text — CAST it to the right type.

Part 3 — Writing JSON

SELECT CustomerID AS customerId, City AS [address.city]     -- dots create nested objects
FROM   Sales.Customer WHERE CustomerID = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;                         -- {"customerId":1,"address":{"city":"London"}}

A correlated subquery with its own FOR JSON PATH produces a nested array. JSON_MODIFY(text, path, value) updates, adds ('append $.tags' adds to an array) or removes (value NULL) a property. JSON_OBJECT and JSON_ARRAY (2022+) build documents from values; SQL Server 2025 adds the aggregates JSON_OBJECTAGG and JSON_ARRAYAGG.

Part 4 — XML

Producing XML:

SELECT ProductCode AS [@code], ProductName AS [name], ListPrice AS [price]
FROM   Inventory.Product WHERE CategoryID = 2
FOR XML PATH('product'), ROOT('products'), TYPE;

[@code] becomes an attribute; plain names become elements; TYPE returns the xml type rather than text.

Querying the xml type — five methods, called on a column or variable:

For element text, (cost/text())[1] is slightly more efficient than (cost)[1].

Part 5 — Storing and indexing documents

• Store JSON in nvarchar(max) with a CHECK (ISJSON(…) = 1) constraint — or in the json type on SQL Server 2025, which validates on input and is stored in an optimised binary format.
• Extract properties you search on into computed columns and index them (Day 26) — OrderRef AS CAST(JSON_VALUE(Payload, '$.orderRef') AS varchar(20)).
• XML has XML indexes (primary and secondary) for large documents; they are big and best for heavy XQuery workloads.
• Shred documents into relational tables when you’ll report on their contents repeatedly; keep the raw document for audit and replay.

C. Dataset and starting state

Setup. Run scripts/lessons/day27_json_xml.sql (download it). It is destructive for its two tables only.

Open a payload to see its shape: SELECT Payload FROM Lab.OrderEvent WHERE EventID = 1;

Validation. The script ends with this check. Expected: 6, 4, 2, 5.

SELECT  (SELECT COUNT(*) FROM Lab.OrderEvent)                                                AS Events,
        (SELECT COUNT(*) FROM Lab.OrderEvent
         WHERE JSON_VALUE(Payload, '$.eventType') = 'OrderPlaced')                          AS OrdersPlaced,
        (SELECT COUNT(*) FROM Lab.SupplierCatalogue)                                         AS Catalogues,
        (SELECT SUM(CatalogueXml.value('count(/catalogue/item)', 'int')) FROM Lab.SupplierCatalogue) AS CatalogueItems;

D. Worked examples

Worked example 1 — Relational to nested JSON

SELECT  c.CustomerID                       AS customerId,
        CONCAT(c.FirstName, N' ', c.LastName) AS name,
        c.City                             AS city,
        (SELECT  o.OrderID     AS orderId,
                 o.OrderDate   AS orderDate,
                 o.OrderStatus AS status,
                 CAST(ROUND(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)), 2) + o.ShippingCost
                      AS decimal(12,2)) AS amountDue
         FROM    Sales.SalesOrder AS o
         JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
         WHERE   o.CustomerID = c.CustomerID
         GROUP BY o.OrderID, o.OrderDate, o.OrderStatus, o.ShippingCost
         ORDER BY o.OrderID
         FOR JSON PATH)                    AS orders
FROM    Sales.Customer AS c
WHERE   c.CustomerID = 35
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Expected result: one value (shown here formatted; SSMS returns it on one line):

{"customerId":35,"name":"Ethan Cole","city":"New York",
 "orders":[{"orderId":1362,"orderDate":"2025-12-21","status":"Shipped","amountDue":33.95},
           {"orderId":1364,"orderDate":"2025-12-22","status":"Shipped","amountDue":476.00}]}

The inner FOR JSON PATH is nested as an array because its result is JSON — SQL Server doesn’t escape it as a string. Dates use ISO format; numbers keep their scale. Click the result in SSMS to open it in a separate window.

Worked example 2 — Extracting values

SELECT  EventID,
        JSON_VALUE(Payload, '$.eventType')                           AS EventType,
        JSON_VALUE(Payload, '$.orderRef')                            AS OrderRef,
        CAST(JSON_VALUE(Payload, '$.customer.id') AS int)            AS CustomerID,
        CAST(JSON_VALUE(Payload, '$.shipping.cost') AS decimal(10,2)) AS ShippingCost,
        JSON_QUERY(Payload, '$.tags')                                AS Tags,
        JSON_VALUE(Payload, '$.lines')                               AS LinesAsValue    -- NULL: it's an array
FROM    Lab.OrderEvent
ORDER BY EventID;

Expected result: 6 rows. EventType/OrderRef: 1 OrderPlaced WEB-5001, 2 OrderPlaced WEB-5002, 3 OrderPlaced WEB-5003, 4 PaymentReceived WEB-5001, 5 OrderCancelled WEB-5002, 6 OrderPlaced WEB-5004. CustomerID: 1, 2, 40, NULL, NULL, NULL. ShippingCost: 4.99, 9.99, 4.99, NULL, NULL, 0.00. Tags: [ "gift" ], [], NULL, NULL, NULL, [ "guest", "click-and-collect" ] (whitespace as stored). LinesAsValue is NULL everywhere — use JSON_QUERY for arrays and objects. Missing paths (lax mode) give NULL, not errors.

Worked example 3 — Shredding an array

OPENJSON with its default schema returns each array element’s position (key, starting at 0) and its JSON (value); a second OPENJSON … WITH turns each element into typed columns:

SELECT  JSON_VALUE(e.Payload, '$.orderRef')  AS OrderRef,
        CAST(a.[key] AS int) + 1             AS LineNumber,
        l.Sku, l.Qty, l.Price, p.ListPrice,
        CASE WHEN p.ProductID IS NULL    THEN 'Unknown SKU'
             WHEN l.Price <> p.ListPrice THEN 'Price differs'
             ELSE 'OK' END                   AS CheckResult
FROM    Lab.OrderEvent AS e
CROSS APPLY OPENJSON(e.Payload, '$.lines') AS a                       -- key = 0, 1, 2…; value = element JSON
CROSS APPLY OPENJSON(a.value)
        WITH (Sku   varchar(20)   '$.sku',
              Qty   int           '$.qty',
              Price decimal(10,2) '$.price') AS l
LEFT JOIN Inventory.Product AS p ON p.ProductCode = l.Sku
WHERE   JSON_VALUE(e.Payload, '$.eventType') = 'OrderPlaced'
ORDER BY OrderRef, LineNumber;

Expected result: 7 rows, all OK:

Events without a lines array (payment, cancellation) produce no rows from CROSS APPLY — use OUTER APPLY to keep them. The column is called LineNumber because LINENO is a reserved word in T-SQL.

Worked example 4 — Shredding XML

SELECT  s.SupplierID,
        s.CatalogueXml.value('(/catalogue/@supplier)[1]', 'nvarchar(60)') AS Supplier,
        s.CatalogueXml.value('(/catalogue/@currency)[1]', 'char(3)')      AS Currency,
        i.n.value('@sku', 'varchar(20)')                                  AS Sku,
        i.n.value('(cost/text())[1]', 'decimal(10,2)')                    AS SupplierCost,
        i.n.value('(leadTimeDays/text())[1]', 'int')                      AS LeadTimeDays,
        i.n.exist('discontinued')                                         AS IsDiscontinued,
        p.StandardCost
FROM    Lab.SupplierCatalogue AS s
CROSS APPLY s.CatalogueXml.nodes('/catalogue/item') AS i(n)
LEFT JOIN Inventory.Product AS p ON p.ProductCode = i.n.value('@sku', 'varchar(20)')
ORDER BY s.SupplierID, Sku;

Expected result:

.nodes() turns each <item> into a row; .value() reads from that node (relative paths) or from the whole document (absolute paths). Note the trap in the last two rows: EUR costs can’t be compared with GBP standard costs without an exchange rate.

E. Guided practical lab — An order-event processor

Turn the JSON events into an order status summary and return it as JSON.

Step 1 — Validate required properties

1. Do: Every OrderPlaced event must have an order reference, a customer email and at least one line.
2. Run:

   USE RetailLab;
   GO
   SELECT  EventID,
           JSON_PATH_EXISTS(Payload, '$.orderRef')       AS HasOrderRef,
           JSON_PATH_EXISTS(Payload, '$.customer.email') AS HasEmail,
           (SELECT COUNT(*) FROM OPENJSON(Payload, '$.lines')) AS LineCount
   FROM    Lab.OrderEvent
   WHERE   JSON_VALUE(Payload, '$.eventType') = 'OrderPlaced'
   ORDER BY EventID;

3. Observe: 4 rows (events 1, 2, 3, 6), each 1, 1 with line counts 2, 1, 3, 1.
4. Why it matters: ISJSON only proves the text is JSON; business validation checks the content.
5. Confirm: no zeros.

Step 2 — Order headers into a temporary table

1. Do: Shred one row per placed order.
2. Run:

   DROP TABLE IF EXISTS #OrderHeader;
   SELECT  h.OrderRef, h.CustomerID, h.Email, h.ShippingMethod, h.ShippingCost, e.ReceivedAt
   INTO    #OrderHeader
   FROM    Lab.OrderEvent AS e
   CROSS APPLY OPENJSON(e.Payload)
           WITH (EventType      varchar(30)   '$.eventType',
                 OrderRef       varchar(20)   '$.orderRef',
                 CustomerID     int           '$.customer.id',
                 Email          varchar(100)  '$.customer.email',
                 ShippingMethod varchar(20)   '$.shipping.method',
                 ShippingCost   decimal(10,2) '$.shipping.cost') AS h
   WHERE   h.EventType = 'OrderPlaced';
   
   SELECT * FROM #OrderHeader ORDER BY OrderRef;

3. Observe: 4 rows: WEB-5001 (1, Standard, 4.99), WEB-5002 (2, Express, 9.99), WEB-5003 (40, Standard, 4.99), WEB-5004 (NULL, guest.buyer@example.com, Collect, 0.00).
4. Why it matters: OPENJSON on the whole document with nested paths in WITH reads many properties in one pass — tidier than many JSON_VALUE calls.
5. Confirm: the guest’s CustomerID is NULL, not an error.

Step 3 — Order totals

1. Do: Sum the lines and add shipping.
2. Run:

   DROP TABLE IF EXISTS #OrderTotal;
   SELECT  h.OrderRef,
           SUM(l.Qty * l.Price) + h.ShippingCost AS OrderTotal,
           COUNT(*)                              AS LineCount
   INTO    #OrderTotal
   FROM    #OrderHeader AS h
   JOIN    Lab.OrderEvent AS e ON JSON_VALUE(e.Payload, '$.orderRef')  = h.OrderRef
                              AND JSON_VALUE(e.Payload, '$.eventType') = 'OrderPlaced'
   CROSS APPLY OPENJSON(e.Payload, '$.lines')
           WITH (Qty int '$.qty', Price decimal(10,2) '$.price') AS l
   GROUP BY h.OrderRef, h.ShippingCost;
   
   SELECT * FROM #OrderTotal ORDER BY OrderRef;

3. Observe: WEB-5001 431.99 (2 lines), WEB-5002 138.99 (1), WEB-5003 100.98 (3), WEB-5004 199.00 (1).
4. Why it matters: Once shredded, documents are just rows — everything from Weeks 1–3 applies.
5. Confirm: 4 rows.

Step 4 — Status from the other events

1. Do: Combine payments and cancellations.
2. Run:

   SELECT  t.OrderRef, t.OrderTotal,
           CASE WHEN c.OrderRef IS NOT NULL       THEN 'Cancelled'
                WHEN p.Paid >= t.OrderTotal       THEN 'Paid'
                WHEN p.Paid > 0                   THEN 'Part paid'
                ELSE 'Awaiting payment' END        AS OrderState
   FROM    #OrderTotal AS t
   LEFT JOIN (SELECT JSON_VALUE(Payload, '$.orderRef') AS OrderRef,
                     SUM(CAST(JSON_VALUE(Payload, '$.payment.amount') AS decimal(10,2))) AS Paid
              FROM   Lab.OrderEvent
              WHERE  JSON_VALUE(Payload, '$.eventType') = 'PaymentReceived'
              GROUP BY JSON_VALUE(Payload, '$.orderRef')) AS p ON p.OrderRef = t.OrderRef
   LEFT JOIN (SELECT DISTINCT JSON_VALUE(Payload, '$.orderRef') AS OrderRef
              FROM   Lab.OrderEvent
              WHERE  JSON_VALUE(Payload, '$.eventType') = 'OrderCancelled') AS c ON c.OrderRef = t.OrderRef
   ORDER BY t.OrderRef;

3. Observe: WEB-5001 Paid, WEB-5002 Cancelled, WEB-5003 Awaiting payment, WEB-5004 Awaiting payment.
4. Why it matters: Event streams describe what happened; state is derived by combining events.
5. Confirm: 4 rows.

Step 5 — Return the summary as JSON

1. Do: Wrap step 4’s query in FOR JSON.
2. Run: add FOR JSON PATH, ROOT('orders') after the ORDER BY of step 4, and alias the columns orderRef, total, state.
3. Observe: {"orders":[{"orderRef":"WEB-5001","total":431.99,"state":"Paid"},{"orderRef":"WEB-5002","total":138.99,"state":"Cancelled"},{"orderRef":"WEB-5003","total":100.98,"state":"Awaiting payment"},{"orderRef":"WEB-5004","total":199.00,"state":"Awaiting payment"}]}
4. Why it matters: The round trip — JSON in, relational processing, JSON out — is the typical integration pattern.
5. Confirm: ISJSON() of the result is 1.

F. Independent practice

Task 1 — Modify documents

Using JSON_MODIFY: (a) add "status": "Cancelled" to event 2; (b) append "vip" to event 1’s tags; (c) remove shipping.city from event 3.

Success criteria: event 2: JSON_VALUE(Payload, '$.status') = Cancelled; event 1: OPENJSON(Payload, '$.tags') returns 2 rows, gift and vip; event 3: JSON_PATH_EXISTS(Payload, '$.shipping.city') = 0 while $.shipping.cost is still 4.99. Every payload still passes the CHECK constraint.

Task 2 — Relational to XML and back

Produce the products in category 2 as XML — root <products>, one <product code="…"> each with <name> and <price> — into an xml variable. Then, from the variable, return the number of products and the name of the most expensive one.

Success criteria: 4 <product> elements; most expensive Classic Canvas Bell Tent (599.00).

Task 3 — Make a JSON property searchable

Add a computed column OrderRef AS CAST(JSON_VALUE(Payload, '$.orderRef') AS varchar(20)) to Lab.OrderEvent, index it, and list all events for WEB-5001 and for WEB-5002.

Success criteria: WEB-5001 → events 1 and 4; WEB-5002 → events 2 and 5. The query uses IX_OrderEvent_OrderRef (with six rows the optimiser may still prefer a scan — add WITH (INDEX(IX_OrderEvent_OrderRef)) once to see the seek).

Task 4 — Query the XML with conditions

List catalogue items with a lead time over 10 days that are not discontinued, with supplier name and currency — filtering inside XQuery or after shredding.

Success criteria: 2 rows: TNT-001 (14 days) and TNT-002 (21 days), both Northern Outdoor Supply, GBP.

Stretch challenge — SQL Server 2025 JSON features

(a) Create a table variable or table with a column of the native json type and insert event 1’s payload. (b) Using JSON_ARRAYAGG, return one row per order of customer 35 with a JSON array of its product codes in code order. (c) Try inserting N'{"broken": }' into the json column.

Success criteria: (b) 1362 → ["BPK-003"]; 1364 → ["COK-002","FTW-002","SLP-001"]. (c) fails with a JSON parsing error. If your build reports json or JSON_ARRAYAGG as unknown, check the version (SELECT @@VERSION;) — these require SQL Server 2025.

G. Solutions and explanations

UPDATE Lab.OrderEvent SET Payload = JSON_MODIFY(Payload, '$.status', 'Cancelled')     WHERE EventID = 2;
UPDATE Lab.OrderEvent SET Payload = JSON_MODIFY(Payload, 'append $.tags', 'vip')       WHERE EventID = 1;
UPDATE Lab.OrderEvent SET Payload = JSON_MODIFY(Payload, '$.shipping.city', NULL)      WHERE EventID = 3;

SELECT JSON_VALUE(Payload, '$.status') FROM Lab.OrderEvent WHERE EventID = 2;
SELECT t.value AS Tag FROM Lab.OrderEvent CROSS APPLY OPENJSON(Payload, '$.tags') AS t WHERE EventID = 1;
SELECT JSON_PATH_EXISTS(Payload, '$.shipping.city') AS HasCity,
       JSON_VALUE(Payload, '$.shipping.cost')       AS Cost
FROM   Lab.OrderEvent WHERE EventID = 3;

DECLARE @x xml =
(
    SELECT ProductCode AS [@code], ProductName AS [name], ListPrice AS [price]
    FROM   Inventory.Product
    WHERE  CategoryID = 2
    ORDER BY ProductCode
    FOR XML PATH('product'), ROOT('products'), TYPE
);

SELECT @x AS ProductsXml;

SELECT  @x.value('count(/products/product)', 'int') AS Products;

SELECT TOP (1) p.n.value('(name/text())[1]', 'nvarchar(100)')   AS ProductName,
               p.n.value('(price/text())[1]', 'decimal(10,2)')  AS Price
FROM   @x.nodes('/products/product') AS p(n)
ORDER BY Price DESC;

ALTER TABLE Lab.OrderEvent ADD OrderRef AS CAST(JSON_VALUE(Payload, '$.orderRef') AS varchar(20));
CREATE INDEX IX_OrderEvent_OrderRef ON Lab.OrderEvent (OrderRef);

SELECT EventID, OrderRef, JSON_VALUE(Payload, '$.eventType') AS EventType
FROM   Lab.OrderEvent
WHERE  OrderRef IN ('WEB-5001', 'WEB-5002')
ORDER BY OrderRef, EventID;

-- Filter inside XQuery
SELECT  s.CatalogueXml.value('(/catalogue/@supplier)[1]', 'nvarchar(60)') AS Supplier,
        s.CatalogueXml.value('(/catalogue/@currency)[1]', 'char(3)')      AS Currency,
        i.n.value('@sku', 'varchar(20)')                                  AS Sku,
        i.n.value('(leadTimeDays/text())[1]', 'int')                      AS LeadTimeDays
FROM    Lab.SupplierCatalogue AS s
CROSS APPLY s.CatalogueXml.nodes('/catalogue/item[leadTimeDays > 10][not(discontinued)]') AS i(n)
ORDER BY Sku;

-- (a) Native json type (SQL Server 2025)
DECLARE @Docs TABLE (DocID int PRIMARY KEY, Doc json NOT NULL);
INSERT INTO @Docs (DocID, Doc) SELECT EventID, Payload FROM Lab.OrderEvent WHERE EventID = 1;
SELECT DocID, JSON_VALUE(Doc, '$.orderRef') AS OrderRef FROM @Docs;

-- (b) JSON_ARRAYAGG
SELECT  o.OrderID,
        JSON_ARRAYAGG(p.ProductCode ORDER BY p.ProductCode) AS ProductCodes
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID   = o.OrderID
JOIN    Inventory.Product    AS p ON p.ProductID = l.ProductID
WHERE   o.CustomerID = 35
GROUP BY o.OrderID
ORDER BY o.OrderID;

-- (c) Invalid JSON is rejected on input
INSERT INTO @Docs (DocID, Doc) VALUES (2, N'{"broken": }');

H. Knowledge check

1. Understanding. When do you use JSON_VALUE, and when JSON_QUERY?

2. Query interpretation. SELECT JSON_VALUE(Payload, '$.customer.id') FROM Lab.OrderEvent WHERE EventID = 6; returns NULL. Is the property missing?

3. Debugging. .value('/catalogue/item/cost', 'decimal(10,2)') fails with ‘value()’ requires a singleton (or empty sequence). Fix it.

4. Understanding. Why is CROSS APPLY OPENJSON(…, '$.lines') the right way to report on order lines stored in JSON, rather than JSON_VALUE(Payload, '$.lines[0].sku'), '$.lines[1].sku', …?

5. Understanding. Name two ways to make searching on a JSON property efficient.

I. Troubleshooting and cleanup

Cleanup. Optional — re-running the Day 27 script resets both tables. To remove them:

DROP TABLE IF EXISTS Lab.OrderEvent, Lab.SupplierCatalogue;

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 produced the nested JSON for customer 35.
• The 7 shredded order lines all matched list prices.
• The 5 catalogue items shredded, with the EUR/GBP caveat noted.
• Order states: Paid, Cancelled, Awaiting payment, Awaiting payment — returned as JSON.
• Tasks 1–4 match the success criteria.

Recap

• JSON paths: $.a.b, $.arr[0]; lax by default (missing → NULL), strict to raise errors.
• JSON_VALUE for scalars, JSON_QUERY for objects/arrays, OPENJSON … WITH + CROSS APPLY for arrays.
• FOR JSON PATH with aliases and nested subqueries builds documents; JSON_MODIFY edits them.
• XML: FOR XML PATH … ROOT … TYPE to produce; .value, .query, .exist, .nodes, .modify to consume.
• Validate on input (ISJSON constraint or the 2025 json type); index searched properties with computed columns; shred for repeated reporting.

Next: Day 28 — Dynamic SQL and metaprogramming: sp_executesql, parameterisation, QUOTENAME, identifier validation and injection prevention.

References (Microsoft Learn)

• JSON data in SQL Server
• OPENJSON (Transact-SQL)
• JSON_VALUE (Transact-SQL)
• Format query results as JSON with FOR JSON
• JSON data type
• JSON_ARRAYAGG (Transact-SQL)
• FOR XML (SQL Server)
• xml data type methods