MSSQL: select json (per versioni precedenti al 2016)

SQL Server 2016 ha introdotto il supporto incorporato per la memorizzazione, la gestione e l’analisi dei dati JSON. Anche se è possibile memorizzare, gestire e analizzare i dati JSON anche nelle versioni precedenti di SQL Server, ma con uno sforzo maggiore. Con il supporto built-in, è possibile farlo molto rapidamente mentre ci si concentra sulla analisi dei dati o la logica dell’applicazione piuttosto che concentrarsi sulla scrittura di funzioni per analizzare i dati JSON. Per chi è ancora alle prese con le versioni precedenti ed ha necessita di restituire dalla select un json può provare questa view:

IF OBJECT_ID (N'dbo.FlattenedJSON') IS NOT NULL
   DROP FUNCTION dbo.FlattenedJSON
GO
CREATE FUNCTION dbo.FlattenedJSON (@XMLResult XML)
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
Begin
Declare  @JSONVersion NVarchar(max), @Rowcount int
Select @JSONVersion = '', @rowcount=count(*) from @XMLResult.nodes('/root/*') x(a)
Select @JSONVersion=@JSONVersion+
Stuff(
  (Select TheLine from 
    (Select ',
    {'+
      Stuff((Select ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(255)'),'')+'":"'+
       Replace( --escape tab properly within a value
         Replace( --escape return properly
           Replace( --linefeed must be escaped
             Replace( --backslash too
               Replace(coalesce(b.c.value('text()[1]','NVARCHAR(MAX)'),''),--forwardslash
               '\', '\\'),   
              '/', '\/'),   
          CHAR(10),'\n'),   
         CHAR(13),'\r'),   
       CHAR(09),'\t')   
     +'"'   
     from x.a.nodes('*') b(c) 
     for xml path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')+'}'
   from @XMLResult.nodes('/root/*') x(a)
   ) JSON(theLine)
  for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )
,1,1,'')
if @Rowcount>1 Return '['+@JSONVersion+'
]'
return @JSONVersion
end