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"}热门推荐
10 祝女儿简短祝福语大全
11 大学新年祝福语简短创意
12 元旦适合的祝福语简短
13 朋友出远门祝福语简短
14 初六简短的祝福语
15 祝男孩生日祝福语简短
16 同事调离的祝福语简短
17 拜年红包的祝福语简短
18 妈妈生日祝福语简短励志