SQL 2016-将XML转换为Json [英] SQL 2016 - Converting XML to Json

问题描述

我正在尝试在SQL2016中使用FOR JSON PATH将XML列转换为Json，但遇到一些问题.给定以下XML(请注意，某些Product元素可能在其中包含Product列表):

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>

我可以在SQL上运行以下语句(其中@xml是上面的XML):

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

它生成的Json是这样的:

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 }]...

您所看到的问题是，在Json生成的所有元素中，所有元素都以所有Product结尾，而与它们的父级关系无关.

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.

我想我想念WHERE子句，在那里我会检查它是否属于父节点，但我不知道怎么做.

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.

我尝试添加一个节点Product [@ id ="3123"](请参见注释行)，但是我需要将"3123"替换为实际的父ID，但我不知道该怎么做.

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.

另一种选择是实际保存父ID(请参见注释行ParentId)，然后在结果中使用JSON_MODIFY删除不匹配但我也没有成功的元素.

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?

-编辑 这是我所期望的Json:

-- 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}]}]}]

您可以假设，如果Level = 1，则没有产品子级别；如果Level = 2，则有产品子级别.

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.

谢谢

推荐答案

内部节点集上的XPath正在从XML中选择所有节点，而不仅仅是外部节点的子节点.

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

(我没有SQL2016的副本，但类似的东西应该可以工作.)

(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

这篇关于SQL 2016-将XML转换为Json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！