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
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
#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