convert XML attribute to json

1
votes

As a simplified example, consider this table with two fields. One is a string and the other is XML.

SELECT TOP (1) [Source]
      , OrderParameter
  FROM [Rops].[dbo].[PreOrder]
  • Source="MediaConversions"
  • OrderParameter="<?xml version="1.0" encoding="utf-16"?>"

Now I want to query the table and have the results as json, but also have the XML converted as json in one go.

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

results in

[{"Source":"MediaConversions","OrderParameter":"<ParameterList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />"}]

But I want it to be converted into:

[{"Source":"MediaConversions","OrderParameter":{ "ParameterList": [ "x": 1, "y": 10] } ]

How to add "for json" to have the xml converted?

SELECT TOP (1) [Source]
      , select OrderParameter for json path????
  FROM [Rops].[dbo].[PreOrder]
for json path;
1
jsonsql-serverxml
I don't understand why your "results in" can contain <ParameterList ... etc., when the entered data for OrderParameter is <?xml version="1.0" encoding="utf-16"?>. I created a temp table in MS SQL Server and ran your second query (the one that ends in for json path), which gave me [{"Source":"MediaConversions","OrderParameter":"<?xml version=\"1.0\" encoding=\"utf-16\"?>"}]. I'm not sure I see the problem. – RobJarvis

1 Answers

2
votes

It looks like you want to pull out the inner text of the ParameterList node inside the XML. You can use .value and XQuery for this:

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;