SQL SERVER 将XML变量转为JSON文本
废话不多说了，直接给大家贴代码了。
--createfunction
createfunction[dbo].[fnXmlToJson](@XmlDataxml)
returnsnvarchar(max)
as
begin
return
(selectstuff(
(select
*
from
(select
',{'+
stuff(
(select
',"'+
coalesce(b.c.value('local-name(.)','NVARCHAR(MAX)'),'')+'":"'+b.c.value('text()[]','NVARCHAR(MAX)')+'"'
fromx.a.nodes('*')b(c)forxmlpath(''),type).value('(./text())[]','NVARCHAR(MAX)'),,,'')
+'}'
from@XmlData.nodes('/root/*')x(a))JSON(theLine)
forxmlpath(''),type).value('.','NVARCHAR(MAX)')
,,,''));
end;
go
--testtableanddata
createtable[dbo].[PivotExample]
(
[Country][nvarchar]()null
,[Year][smallint]notnull
,[SalesAmount][money]null
)
on
[PRIMARY];
insertinto[dbo].[PivotExample]values('Australia',,.);
insertinto[dbo].[PivotExample]values('Germany',,.);
insertinto[dbo].[PivotExample]values('UnitedStates',,.);
insertinto[dbo].[PivotExample]values('France',,.);
declare@xmlxml;
set@xml=(selecttop*from[dbo].[PivotExample]forxmlpath,root);
selectdbo.fnXmlToJson(@xml);
--returnstring
{"Country":"Australia","Year":"","SalesAmount":"."},
{"Country":"Germany","Year":"","SalesAmount":"."},
{"Country":"UnitedStates","Year":"","SalesAmount":"."},
{"Country":"France","Year":"2008","SalesAmount":"922179.0400"}