Converting XML Documents to JSON With FOR JSON PATH

Converting XML documents to JSON works best when you decide the target shape first and then build it with FOR JSON PATH. The conversion is not automatic. Every attribute, repeated element and missing child needs a decision.

A partner wants JSON from your XML

Imagine an older system that exports orders as XML. A partner now wants the same data as JSON. Someone says “just convert it,” and that is where the guessing starts. Is an attribute a number? Is a repeated line an array? What should an order with no lines look like?

I like to answer those questions with a tiny example. Here are two orders. Order 1 has two lines, deliberately in the wrong order. Order 2 has none. The XML is stored in a temp table, since XML methods need QUOTED_IDENTIFIER ON, which is the default in SSMS.

SET NOCOUNT ON;
DROP TABLE IF EXISTS #Source;
DROP TABLE IF EXISTS #Result;
CREATE TABLE #Source (Doc xml NOT NULL);
INSERT #Source (Doc) VALUES (N'<orders>
  <order id="1"><customer>Ada</customer><line sku="B" qty="1"/><line sku="A" qty="2"/></order>
  <order id="2"><customer>Lee</customer></order>
</orders>');

Build the nested result

The mapping is simple. The id attribute becomes a number. The customer element becomes a nested name, written with a dotted alias. The repeated line elements become an array, sorted by sku so the output is the same every time. In real data, use a proper line number if sku can repeat.

CREATE TABLE #Result (Json nvarchar(max) NOT NULL);
INSERT #Result (Json)
SELECT (
    SELECT o.n.value('(@id)[1]', 'int') AS id,
           o.n.value('(customer/text())[1]', 'nvarchar(100)') AS [customer.name],
           (SELECT l.n.value('(@sku)[1]', 'nvarchar(20)') AS sku,
                   l.n.value('(@qty)[1]', 'int') AS quantity
            FROM o.n.nodes('line') AS l(n)
            ORDER BY l.n.value('(@sku)[1]', 'nvarchar(20)')
            FOR JSON PATH) AS lines
    FROM #Source AS s
    CROSS APPLY s.Doc.nodes('/orders/order') AS o(n)
    ORDER BY o.n.value('(@id)[1]', 'int')
    FOR JSON PATH
);
SELECT Json AS JsonDocument, ISJSON(Json) AS IsValidJson FROM #Result;

ISJSON returns 1. The output is an array with two orders. Order 1 has customer.name Ada and lines A then B, with quantities 2 and 1. Order 2 has customer.name Lee and no lines property at all. The line elements came out sorted, even though the XML listed B first.

Read it back with OPENJSON

Valid syntax is not the same as the right shape. So I read the result back into typed columns and look at it the way the partner will.

SELECT j.id, j.customerName, j.lines
FROM #Result AS r
CROSS APPLY OPENJSON(r.Json)
    WITH (id int '$.id',
          customerName nvarchar(100) '$.customer.name',
          lines nvarchar(max) '$.lines' AS JSON) AS j
ORDER BY j.id;

Order 1 comes back with Ada and its lines array. Order 2 comes back with Lee and NULL in lines. The missing property shows up as NULL, so the receiver has to treat “missing” and “empty” as the same thing, or you must pick one.

Choose how an empty order looks

Many receivers want lines to always exist. You have two options. INCLUDE_NULL_VALUES writes “lines”:null. Or you can replace the missing array with an empty one, [].

SELECT (
    SELECT o.n.value('(@id)[1]', 'int') AS id,
           (SELECT l.n.value('(@sku)[1]', 'nvarchar(20)') AS sku
            FROM o.n.nodes('line') AS l(n)
            FOR JSON PATH) AS lines
    FROM #Source AS s CROSS APPLY s.Doc.nodes('/orders/order') AS o(n)
    ORDER BY o.n.value('(@id)[1]', 'int')
    FOR JSON PATH, INCLUDE_NULL_VALUES
) AS WithNull,
(
    SELECT o.n.value('(@id)[1]', 'int') AS id,
           JSON_QUERY(ISNULL((SELECT l.n.value('(@sku)[1]', 'nvarchar(20)') AS sku
                              FROM o.n.nodes('line') AS l(n)
                              FOR JSON PATH), N'[]')) AS lines
    FROM #Source AS s CROSS APPLY s.Doc.nodes('/orders/order') AS o(n)
    ORDER BY o.n.value('(@id)[1]', 'int')
    FOR JSON PATH
) AS WithEmptyArray;

The first column shows “lines”:null for order 2. The second shows “lines”:[]. Both are valid, and the receiver may accept only one. Ask before you ship. I left out the sort here to keep the query short.

Why JSON_QUERY keeps arrays real

Look at the second query above again. It wraps the nested array in ISNULL, and then in JSON_QUERY. Without that wrapper, SQL Server treats the text as an ordinary string and escapes it. Here is the difference on a tiny value.

DECLARE @lines nvarchar(max) = N'[{"sku":"A","quantity":2}]';
SELECT (SELECT 1 AS id, @lines AS lines FOR JSON PATH) AS WithoutJsonQuery,
       (SELECT 1 AS id, JSON_QUERY(@lines) AS lines FOR JSON PATH) AS WithJsonQuery;

The first column has backslashes everywhere, because the array was turned into a string. The second has a real nested array. Whenever the nested JSON comes from a variable, a column or an expression, wrap it in JSON_QUERY. A direct FOR JSON subquery, like the one in the build step, does not need it.

Clean up

DROP TABLE IF EXISTS #Result;
DROP TABLE IF EXISTS #Source;

One last warning. A valid JSON result only proves the syntax. Check required properties, number types, nulls and array order against what the receiver expects. If your XML uses namespaces, the extraction needs to know about them too.

Agree on the receiving shape before you write the conversion query.

JSON conversion is not format preservation, it is an explicit mapping between structures.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.