SQL 2016 - Converting XML to Json
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
Sibele Lima
I am a MCSD Full Stack Senior Software Developer with experience in each layer of software development and a genuine interest in all software technologies. I am a Bachelor in Computer Science with a Graduate Diploma in IT Management.
Updated on June 09, 2022Comments
-
Sibele Lima almost 2 years
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