废话不多说了，直接给大家贴代码了。
-- 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"}
【SQL SERVER 将XML变量转为JSON文本】相关文章：
★ 精妙的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导出转换
★ SQL Server 2005 定时执行SQL语句的方法