SQL SERVER 将XML变量转为JSON文本_Sql Server

SQL SERVER 将XML变量转为JSON文本

2020-05-03 15:31和尚洗头用飘柔 Sql Server

这篇文章主要介绍了SQL SERVER 将XML变量转为JSON文本的相关资料,需要的朋友可以参考下

废话不多说了，直接给大家贴代码了。

									-- create function

									create function [dbo].[fnXmlToJson] (@XmlData xml)

									returns nvarchar(max)

									as

									begin

									return

									(select stuff( 

									(select

									*

									from 

									(select

									',{'+ 

									stuff(

									(select

									',"'+

									coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+ b.c.value('text()[]','NVARCHAR(MAX)') +'"'

									from x.a.nodes('*') b(c) for xml path(''),type).value('(./text())[]','NVARCHAR(MAX)'),,,'')

									+'}'

									from @XmlData.nodes('/root/*') x(a)) JSON(theLine) 

									for xml path(''),type).value('.','NVARCHAR(MAX)' )

									,,,''));

									end;

									go

									-- test table and data

									create table [dbo].[PivotExample]

									(

									[Country] [nvarchar]() null

									,[Year] [smallint] not null

									,[SalesAmount] [money] null

									)

									on

									[PRIMARY];

									insert into [dbo].[PivotExample]values('Australia', , .);

									insert into [dbo].[PivotExample]values('Germany', , .);

									insert into [dbo].[PivotExample]values('United States', , .);

									insert into [dbo].[PivotExample]values('France', , .);

									declare @xml xml;

									set @xml=(select top * from [dbo].[PivotExample] for xml path, root);

									select dbo.fnXmlToJson(@xml);

									--return string

									{"Country":"Australia","Year":"","SalesAmount":"."},

									{"Country":"Germany","Year":"","SalesAmount":"."},

									{"Country":"United States","Year":"","SalesAmount":"."},

									{"Country":"France","Year":"2008","SalesAmount":"922179.0400"}