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"}热门推荐
1 如何训练狗狗不叫
2 训练狗狗不叫
3 训练狗狗随行
4 怎么训练狗狗不叫
7 训练比熊狗狗教程
10 上海狗狗训练
11 怎么训练狗狗上厕所
12 训练狗狗趴下
13 狗狗服从训练
14 训练狗狗拒食
15 训练狗狗笑
16 训练狗狗大便
17 训练狗狗立
18 训练狗狗捡东西