SQL 2016 - Converting XML to Json

Question

I'm trying to convert a XML column to Json using FOR JSON PATH in SQL2016 but I'm having some issues. Given the following XML (note that some Product elements might have a list of Product inside):

  <Request>
    <SelectedProducts>
      <Product id="D04C01S01" level="1" />
      <Product id="158796" level="1" />
      <Product id="7464" level="2">
        <Product id="115561" level="3" />
      </Product>
      <Product id="907" level="2">
        <Product id="12166" level="3" />
        <Product id="33093" level="3" />
        <Product id="33094" level="3" />
        <Product id="28409" level="3" />
      </Product>
      <Product id="3123" level="2">
        <Product id="38538" level="3" />
        <Product id="37221" level="3" />
      </Product>
    </SelectedProducts>    
  </Request>

I can run the following statement on SQL (where @xml is the XML above):

SELECT 
     d.value('./@id', 'varchar(50)') AS 'Id'
    ,d.value('./@level', 'int') AS 'Level'
    ,(SELECT 
        --f.value('../@id', 'varchar(50)') AS 'ParentId'
        f.value('./@id', 'varchar(50)') AS 'Id'
        ,f.value('./@level', 'int') AS 'Level'
        --FROM @xml.nodes('/Request/SelectedProducts/Product[@id="3123"]/Product') AS e(f)          
        FROM @xml.nodes('/Request/SelectedProducts/Product/Product') AS e(f)            
        FOR JSON PATH) 'Product'
    FROM @xml.nodes('/Request/SelectedProducts/Product') AS c(d)
    FOR JSON PATH

The Json it generates is something like this:

[{"Id":"D04C01S01", 
  "Level":2,
  "Product":[{"Id":"115561", "Level":3 }, {"Id":"12166","Level":3 }, { Id":"33093", "Level":3 }, {"Id":"33094","Level":3 }, {"Id":"28409","Level":3},
{"Id":"38538","Level":3},{"Id":"37221","Level":3 }]},

{"Id":"158796", 
  "Level":3,
  "Product":[{"Id":"115561", "Level":3 }, {"Id":"12166","Level":3 }, { Id":"33093", "Level":3 }, {"Id":"33094","Level":3 }, {"Id":"28409","Level":3},
{"Id":"38538","Level":3},{"Id":"37221","Level":3 }]...

The problem as you can see is that in the Json generated all elements end up with all the Product regardless of their parent relationship.

I guess I am missing a WHERE clause where I would check it belongs to the parent node but I couldn't figure out how.

I tried to add a nodes Product[@id="3123"] (see commented line) but I need to replace the "3123" for the actual parent id and I don't know how to do it.

Another option was to actually save the parent id (see the commented line ParentId) and then using JSON_MODIFY in the result to delete the elements that didn't match but I wasn't successful also.

Does anyone have any ideas on how I can fix this? Or what else I could do?

-- EDIT This is the Json that I am expecting:

[{"Request": 
[{"Id":"D04C01S01","Level":1 }, 
{"Id":"158796","Level":1},
{"Id":"7464","Level":2,"Product":[{"Id":"115561","Level":3}]},
{"Id":"907","Level":2,"Product":[{"Id":"12166","Level":3},{"Id":"33093","Level":3},{"Id":"33094","Level":3},{"Id":"28409","Level":3}]},
{"Id":"3123","Level":2,"Product":[{"Id":"38538","Level":3},{"Id":"37221","Level":3}]}]}]

You may assume that if Level=1 then there will not be a Product sub-level and if Level=2 then there will be a Product sub-level.

Thank you

Answer 1

I do not quite understand the level values. The Products with level="1" seem not to have any sub-products. On the same (hierachical) level within your XML there are level="2" products, with nested level="3" products. Is this valid for all cases?

If so, you need to query your XML in two steps using OUTER APPLY:

DECLARE @xml XML=
N'<Request>
  <SelectedProducts>
    <Product id="D04C01S01" level="1" />
    <Product id="158796" level="1" />
    <Product id="7464" level="2">
      <Product id="115561" level="3" />
    </Product>
    <Product id="907" level="2">
      <Product id="12166" level="3" />
      <Product id="33093" level="3" />
      <Product id="33094" level="3" />
      <Product id="28409" level="3" />
    </Product>
    <Product id="3123" level="2">
      <Product id="38538" level="3" />
      <Product id="37221" level="3" />
    </Product>
  </SelectedProducts>
</Request>';

SELECT p1.value(N'@id','nvarchar(max)') AS P1_id
      ,p1.value(N'@level','int') AS P1_level
      ,p2.value(N'@id','nvarchar(max)') AS P2_id
      ,p2.value(N'@level','int') AS P2_level
FROM @xml.nodes(N'/Request/SelectedProducts/Product') AS A(p1)
OUTER APPLY A.p1.nodes(N'Product') AS B(p2);

The result

+-----------+----------+--------+----------+
| P1_id     | P1_level | P2_id  | P2_level |
+-----------+----------+--------+----------+
| D04C01S01 | 1        | NULL   | NULL     |
+-----------+----------+--------+----------+
| 158796    | 1        | NULL   | NULL     |
+-----------+----------+--------+----------+
| 7464      | 2        | 115561 | 3        |
+-----------+----------+--------+----------+
| 907       | 2        | 12166  | 3        |
+-----------+----------+--------+----------+
| 907       | 2        | 33093  | 3        |
+-----------+----------+--------+----------+
| 907       | 2        | 33094  | 3        |
+-----------+----------+--------+----------+
| 907       | 2        | 28409  | 3        |
+-----------+----------+--------+----------+
| 3123      | 2        | 38538  | 3        |
+-----------+----------+--------+----------+
| 3123      | 2        | 37221  | 3        |
+-----------+----------+--------+----------+

p1 are all the products situated right below <SelectedProducts>, while p2 are the nested products below another product.

Without an example of the JSON you might need I cannot help you here, but this should get you on some sort of a track ...

Answer 2

Your XPath on the inner node set is selecting all nodes from the XML and not just children of the outer node.

(I don't have a copy of SQL2016 on me but something like this should work.)

SELECT 
    d.value('./@id', 'varchar(50)') AS 'Id'
    ,d.value('./@level', 'int') AS 'Level'
    ,(SELECT 
        f.value('./@id', 'varchar(50)') AS 'Id'
        ,f.value('./@level', 'int') AS 'Level'
        FROM c.d.nodes('./Product') AS e(f)            
        FOR JSON PATH) 'Product'
FROM @xml.nodes('/Request/SelectedProducts/Product') AS c(d)
FOR JSON PATH

Answer 3

As a partial solution, this way you can get hierarhy adjacency pairs from the XML input. Then you need wrap it to JSON by recursion again I believe.

declare @xml xml =
 '<Request>
   <SelectedProducts>
     <Product id="D04C01S01" level="1" />
     <Product id="158796" level="1">
      <Product id="7464" level="2">
        <Product id="115561" level="3" />
      </Product>
      <Product id="907" level="2">
        <Product id="12166" level="3" />
        <Product id="33093" level="3" />
        <Product id="33094" level="3" />
        <Product id="28409" level="3" />
      </Product>
      <Product id="3123" level="2">
        <Product id="38538" level="3" />
        <Product id="37221" level="3" />
      </Product>
     </Product>
   </SelectedProducts>    
  </Request>';
 with cte as (
  SELECT 
     d.value('./@id', 'varchar(50)') AS 'Id'
    ,d.value('./@level', 'int') AS 'Level'
    , CAST(NULL AS varchar(50)) AS 'ParentId'
    ,d.query('./Product') morexml
  FROM @xml.nodes('/Request/SelectedProducts/Product') AS c(d)
  UNION ALL
  SELECT 
     d.value('./@id', 'varchar(50)') AS 'Id'
    ,d.value('./@level', 'int') AS 'Level'
    , Id AS 'ParentId'
    ,d.query('./Product') morexml
  FROM cte
  CROSS APPLY morexml.nodes('Product') AS c(d)
  WHERE morexml IS NOT NULL
)
select Id, Level, ParentId
from cte;

Answer 4

May be you could try to convert all records in your database using C# code like below:

// read record from your table and for column colname
string yourColnameValueXmlIn = '' // assign here your value

// To convert an XML node contained in string xml into a JSON string   
XmlDocument doc = new XmlDocument();
doc.LoadXml(yourColnameValueXml );
string yourColnameValueJSONOut = JsonConvert.SerializeXmlNode(doc);

// assign your new value in json to column in record
// save your updated record