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"}
SQL Server XML变量转为Json文本
最新推荐文章于 2023-06-07 10:50:55 发布