Comment puis-je obtenir un objet JSON à partir d'une table SQL Server?

-- 
-- Author:      Thiago R. Santos                                           --
-- Create date: Aug 3rd 2008                                                   --
-- Description: Returns the contents of a given table                      --
--              in JavaScript Object Notation.                             --
-- Params:                                                                 --
--      @table_name: the table to execute the query                        --
--      @registries_per_request: equivalent to "select top N * from table" 
-- 
--                               replcing N by the actual number           
-- Influenced by Thomas Frank's post MySQL to JSON @ January 23, 2007      --
-- Post Url: http://www.thomasfrank.se/mysql_to_json.html                  --
create procedure [dbo].[GetJSON]
(
@table_name varchar(50),
@registries_per_request smallint = null
)
as
begin
if((select count(*) from information_schema.tables where table_name =   @table_name)     > 0)
begin
declare @json varchar(max),
@line varchar(max),
@columns varchar(max),
@sql nvarchar(max),
@columnNavigator varchar(50),
@counter tinyint,
@size varchar(10)
if (@registries_per_request is null) 
begin
set @size = ''
end
else 
begin
set @size = 'top ' + convert(varchar, @registries_per_request)
end
set @columns = '{'
declare schemaCursor cursor
for select column_name from information_schema.columns where table_name = @table_name
open    schemaCursor    
fetch next from schemaCursor
into  @columnNavigator
select  @counter = count(*) from information_schema.columns where table_name = @table_name
while @@fetch_status = 0
begin
set @columns = @columns + '''''' + @columnNavigator + ''''':'''''' + convert(varchar, ' + @columnNavigator + ') + '''''''
set @counter = @counter - 1
if(0 != @counter) 
begin
set @columns = @columns + ','
end
fetch next from schemaCursor
into  @columnNavigator
end 
set @columns =  @columns + '}'
close       schemaCursor
deallocate  schemaCursor
set @json = '['
set @sql = 'select  ' + @size + '''' + @columns + ''' as json into tmpJsonTable from ' + @table_name
exec sp_sqlexec @sql
select  @counter = count(*) from tmpJsonTable
declare tmpCur cursor
for     select * from tmpJsonTable
open    tmpCur
fetch next from tmpCur
into  @line
while @@fetch_status = 0
begin
set @counter = @counter - 1
set @json = @json + @line
if ( 0 != @counter ) 
begin
set @json = @json + ','
end
fetch next from tmpCur
into  @line
end
set @json = @json + ']'
close       tmpCur
deallocate  tmpCur
drop table  tmpJsonTable
select @json as json
end
end

En dessous de la version est un total de ré-élaboration de ce concept. Si j'ai oublié quelque chose s'il vous plaît ajouter une note et je vais modifier pour ajuster.

--
-- Author:      Matthew D. Erwin (Snaptech, LLC)
-- Create date: May 9, 2013                                                
-- Description: Returns the contents of a given table                      
--              in JavaScript Object Notation JSON - 
--
--              Very notably useful for generating MOCK .json files
--              for testing or before RESTful services are completed.
--
--              This implementation:
--                  *removed cursor (using FOR XML PATH(''))
--                  *properly supports NULL vs quoted values
--                  *supports dates in ISO 8601 - presuming UTC
--                  *uses Data_Type and Is_Nullable info
--                  *escapes '\'
--                  *formats output with tabs/newlines
--                  *can return final results as XML to bypass
--                   truncation in SSMS
--                  *supports schema (e.g. [dbo].[TableName]
--                  *includes "recordCount" field
-- Options:                                                                
--      @table_name: the table to execute the query                        
--      @limit: equivalent to "select top N * from table" 
--      @ssms: flag to use if executing in Sql Server Management Studio
--             to bypass result truncation limits.
-- 
-- Inspired primarily by the 2008 work of Thiago R. Santos which was influenced by Thomas Frank.
-- Usage: [dbo].[GetJSON] @Table_name = 'MySchema.MyTable', @limit = 50, @ssms = 0
create procedure [dbo].[GetJSON] (
@table_name varchar(max), 
@limit int = null,
@ssms bit = 0
)
as
begin
declare @json varchar(max), @query varchar(max), @table_schema varchar(max) = null
if( charindex('.', @table_name) > 0 )
begin
set @table_schema = replace(replace( substring(@table_name, 0, charindex('.',@table_name)), '[', ''), ']', '')
set @table_name = replace(replace( substring(@table_name, charindex('.',@table_name) + 1,len(@table_name)), '[', ''), ']', '')
end
set @query = 
'select ' + case when @limit is not null then 'top ' + cast(@limit as varchar(32)) + ' ' else '' end + '''{ '' + REVERSE(STUFF(REVERSE(''' +
CAST((SELECT ' "' + column_name + '" : ' + 
case when is_nullable = 'YES' 
then ''' + case when [' + column_name + '] is null then ''null'' else ' + 
case when data_type like '%char%' or data_type like '%text%' then '''"'' + ' else '' end + 
case when data_type like '%date%' then 'convert(varchar(23),[' + column_name + '], 126) + ''Z''' else 
'replace(replace(replace(replace(cast([' + column_name + '] as varchar(max)),''\'',''\\''),''"'',''\"''),char(10),''\n''),char(13),''\n'') ' end + 
case when data_type like '%char%' or data_type like '%text%' then '+ ''"''' else '' end + ' end + ''' 
else 
case when data_type like '%char%' or data_type like '%text%' then '"' else '' end + 
''' + ' +
case when data_type like '%date%' then 'convert(varchar(23),[' + column_name + '], 126) + ''Z' else 
'replace(replace(replace(replace(cast([' + column_name + '] as varchar(max)),''\'',''\\''),''"'',''\"''),char(10),''\n''),char(13),''\n'') + ''' end +
case when data_type like '%char%' or data_type like '%text%' then '"' else '' end end + ',' AS [text()] 
from information_schema.columns where table_name = @table_name and (@table_schema is null or table_schema = @table_schema) FOR XML PATH('') ) as varchar(max)) +
'''),1,1,'''')) + '' }'' as json into tmpJsonTable from ' + @table_name + ' with(nolock) '
exec sp_sqlexec @query
set @json = 
'{' + char(10) + char(9) +
'"recordCount" : ' + Cast((select count(*) from tmpJsonTable) as varchar(32)) + ',' + char(10) + char(9) +
'"records" : ' + char(10) + char(9) + char(9) + '[' + char(10)
+ REVERSE(STUFF(REVERSE(CAST((SELECT char(9) + char(9) + json + ',' + char(10) AS [text()] FROM tmpJsonTable FOR XML PATH('')) AS varchar(max))),1,2,''))
+ char(10) + char(9) + char(9) + ']' + char(10) + '}'
drop table tmpJsonTable
if( @ssms = 1 and len(@json) > 65535 ) --deal with Sql Server Management Studio text/grid truncation
select cast('<json><![CDATA[' + @json + ']]></json>' as xml) as jsonString
else
select @json as jsonString
end

De ne pas faire dérailler le cas des OP question, mais je me demande si cela en SQL est le meilleur /les plus appropriés, la route à prendre? Il me semble que ce serait peut-être plus facilement /efficacement accompli dans le code.

J'ai d'abord été demandais la même chose (ce qui est la façon dont j'ai trouvé ce post), mais après la mastication sur l'idée pendant quelques minutes, il semble que cela pourrait être mieux réalisé à l'aide d'un utilitaire /extension de la méthode qui prend un dataset & renvoie la résultante chaîne JSON.

Accordée, l'OP peut avoir de bonnes raisons pour avoir besoin d'aller dans cette voie. Je suis juste la pensée (de frappe) à haute voix ici...