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"}