SQL Server XML变量转为Json文本

 1 -- create function
 2 create function [dbo].[fnXmlToJson] (@XmlData xml)  3 returns nvarchar(max)  4 as  5 begin  6 return  7 (select stuff(  8 (select  9 * 10 from 11 (select 12 ',{'+ 13 stuff( 14 (select 15 ',"'+ 16 coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+ b.c.value('text()[1]','NVARCHAR(MAX)') +'"' 17 from x.a.nodes('*') b(c) for xml path(''),type).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'') 18 +'}' 19 from @XmlData.nodes('/root/*') x(a)) JSON(theLine) 20 for xml path(''),type).value('.','NVARCHAR(MAX)' ) 21 ,1,1,'')); 22 end; 23 go 24 25 -- test table and data 26 create table [dbo].[PivotExample] 27  ( 28 [Country] [nvarchar](50) null 29 ,[Year] [smallint] not null 30 ,[SalesAmount] [money] null 31  ) 32 on 33 [PRIMARY]; 34 35 36 insert into [dbo].[PivotExample]values('Australia', 2005, 1309047.1978); 37 insert into [dbo].[PivotExample]values('Germany', 2006, 521230.8475); 38 insert into [dbo].[PivotExample]values('United States', 2007, 2838512.355); 39 insert into [dbo].[PivotExample]values('France', 2008, 922179.04); 40 41 42 declare @xml xml; 43 set @xml=(select top 4 * from [dbo].[PivotExample] for xml path, root); 44 45 select dbo.fnXmlToJson(@xml); 46 47 --return string 48 {"Country":"Australia","Year":"2005","SalesAmount":"1309047.1978"}, 49 {"Country":"Germany","Year":"2006","SalesAmount":"521230.8475"}, 50 {"Country":"United States","Year":"2007","SalesAmount":"2838512.3550"}, 51 {"Country":"France","Year":"2008","SalesAmount":"922179.0400"}