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