SQL SERVER converts XML variables to JSON text _mssql

Nonsense not to say more, directly to everyone paste code.

--Create function Create function [dbo]. [Fnxmltojson] (@XmlData XML) returns nvarchar (max) as begin return (SELECT Stuff (SELECT * FROM (select), {' + Stuff (select ', ' + ' + C Oalesce (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 "}