SQL Server将XML转换为JSON

I would like to convert an XML column into a JSON string. So far I've tried few options on SO and Google, but all of them convert a table into a JSON string. Is there any way I can do this without converting the xml into a table first?

我想将XML列转换为JSON字符串。到目前为止,我在SO和Google上尝试了很少的选项,但是所有这些选项都将表转换为JSON字符串。有没有办法在没有先将xml转换成表的情况下才能做到这一点?

Version: SQL Server 2014 (SP2)

版本:SQL Server 2014(SP2)

declare @xml xml
select @xml = xml_column from mytable
select some_potential_function(@xml) as JSON_String

PS: My xml grows dynamically as I store a tree with varying number of levels in it. That's why converting it to a table may not be an option.

PS:当我存储一个具有不同级别的树时,我的xml会动态增长。这就是为什么将它转换成表可能不是一个选择。

1 个解决方案

#1

You might try ".nodes()" method on XML type to do this (https://msdn.microsoft.com/en-us/library/ms188282.aspx) along with FOR JSON AUTO in SQL Server 2016 and above (https://msdn.microsoft.com/en-us/library/dn921882.aspx) :

您可以在XML类型上尝试使用“.nodes()”方法(https://msdn.microsoft.com/en-us/library/ms188282.aspx)以及SQL Server 2016及更高版本中的FOR JSON AUTO(https ://msdn.microsoft.com/en-us/library/dn921882.aspx):

DECLARE @xml XML = '<?xml version="1.0"?>
    <catalog>
       <book id="bk101">
          <author>Gambardella, Matthew</author>
       </book>
       <book id="bk102">
          <author>Ralls, Kim</author>
       </book>
    </catalog>''';
SELECT b.value('@id', 'nvarchar(MAX)') AS book
    ,b.value('author[1]', 'nvarchar(MAX)') AS author
    -- the rest of your columns
FROM @xml.nodes('/catalog/book') AS a(b)
FOR JSON AUTO