将XML属性转换为json

Question

作为一个简化的例子，考虑这个有两个字段的表。一个是字符串，另一个是XML。

SELECT TOP (1) [Source]
      , OrderParameter
  FROM [Rops].[dbo].[PreOrder]

• Source=“MediaConversions”

现在我想查询该表，并将结果作为json，但也要一次性将XML转换为json。

SELECT TOP (1) [Source]
      , OrderParameter
  FROM [Rops].[dbo].[PreOrder]
for json path;

导致

[{"Source"："媒体转换"，"OrderParameter"："

但我想把它转换成：

[{"Source"："MediaConversion"，"OrderParameter"：{"ParameterList"：["x"： 1，"y"： 10]}]

如何添加“for json”来转换xml？

SELECT TOP (1) [Source]
      , select OrderParameter for json path????
  FROM [Rops].[dbo].[PreOrder]
for json path;

Answer 1

看起来您想要拉出XML中ParameterList节点的内部文本。您可以使用。值和XQuery：

SELECT TOP (1) [Source]
      , OrderParameter = (
           SELECT
              x = x.PL.value('(x/text())[1]','int'),
              y = x.PL.value('(y/text())[1]','int')
           FROM (VALUES( CAST(OrderParameter AS xml) )) v(OrderParameter)
           CROSS APPLY v.OrderParameter.nodes('ParameterList') x(PL)
           FOR JSON PATH, ROOT('ParameterList')
        )
  FROM [Rops].[dbo].[PreOrder]
FOR JSON PATH;