logo标签列表

SQL 2016 - 将XML转换为Json

sql-serverjsonxmlt-sqlsql-server-2016
5
我将尝试使用SQL2016中的FOR JSON PATH将XML列转换为Json，但是我遇到了一些问题。给定以下XML（请注意，某些Product元素可能包含Product列表）：
  <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）：
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格式大致如下：
[{"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中所有元素最终都与所有产品相关，而不考虑它们的父关系。 我想我漏掉了一个WHERE子句，应该在其中检查它是否属于父节点，但我无法想出如何做到。我尝试添加节点Product[@id="3123"]（请参见已注释的行），但我需要用实际的父ID替换“3123”，而我不知道如何操作。另一个选择是实际上保存父ID（请参见已注释的行ParentId），然后在结果中使用JSON_MODIFY删除不匹配的元素，但我也没有成功。 有人有任何想法，我该如何解决这个问题？ 或者我还能做什么？--编辑这是我期望的Json：
[{"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，则将有一个产品子层级。
谢谢。
发布您期望的JSON将会有所帮助... - Shnugo
好的，我添加了我期望的Json。 - Sibele Lima
4个回答
5

您在内部节点集上的XPath选择了XML中的所有节点，而不仅仅是外部节点的子节点。

（我手头没有SQL2016的副本，但类似这样的内容应该可以正常工作。）

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
0

也许你可以尝试使用以下C#代码将数据库中的所有记录转换：

// 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
OP从未提到任何编程语言。最好不要假设他们使用C#。 - Anthony Horne
0
作为部分解决方案，您可以从XML输入中获取层次邻接对。然后，我认为您需要再次使用递归将其转换为JSON。
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;
0

我不太理解level的值。具有level="1"的产品似乎没有任何子产品。在您的XML中，同一（分层）级别上有嵌套的level="3"产品的level="2"产品。这对所有情况都有效吗？

如果是这样，您需要使用OUTER APPLY进行两步查询您的XML：

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);

结果

+-----------+----------+--------+----------+
| 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 是位于 <SelectedProducts> 下方的所有产品，而 p2 是另一个产品下面的嵌套产品。

如果没有 JSON 示例，我无法在这里为您提供帮助，但这应该能让您有所了解...

1
感谢您的回复。我已经将我期望的Json添加到了问题中。是的，您可以假设Level=1没有子产品，但Level=2会有。@MatthewWhited的解决方案完美地解决了问题。 - Sibele Lima
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接