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