SQL Server table to json

json, sql, sql-server-2008

Solution

I wouldn't really advise it, there are much better ways of doing this in the application layer, but the following avoids loops, and is a lot less verbose than your current method:

CREATE PROCEDURE dbo.GetJSON @ObjectName VARCHAR(255), @registries_per_request smallint = null
AS
BEGIN
    IF OBJECT_ID(@ObjectName) IS NULL
        BEGIN
            SELECT Json = '';
            RETURN
        END;

    DECLARE @Top NVARCHAR(20) = CASE WHEN @registries_per_request IS NOT NULL 
                                    THEN 'TOP (' + CAST(@registries_per_request AS NVARCHAR) + ') ' 
                                    ELSE '' 
                                END;

    DECLARE @SQL NVARCHAR(MAX) = N'SELECT ' + @Top + '* INTO ##T ' + 
                                'FROM ' + @ObjectName;

    EXECUTE SP_EXECUTESQL @SQL;

    DECLARE @X NVARCHAR(MAX) = '[' + (SELECT * FROM ##T FOR XML PATH('')) + ']';


    SELECT  @X = REPLACE(@X, '<' + Name + '>', 
                    CASE WHEN ROW_NUMBER() OVER(ORDER BY Column_ID) = 1 THEN '{'
                         ELSE '' END + Name + ':'),
            @X = REPLACE(@X, '</' + Name + '>', ','),
            @X = REPLACE(@X, ',{', '}, {'),
            @X = REPLACE(@X, ',]', '}]')
    FROM    sys.columns
    WHERE   [Object_ID] = OBJECT_ID(@ObjectName)
    ORDER BY Column_ID;

    DROP TABLE ##T;

    SELECT  Json = @X;

END

N.B. I've changed your two part object name (@schema and @table) to just accept the full object name.

Example on SQL Fiddle

The idea is to basically use the XML extension within SQL-Server to turn the table into XML, then just replace the start tags with `{ColumnName:` and the end tags with `,`. It then requires two more replaces to stop add the closing bracket to the last column of each row, and the remove the final `,` from the JSON string.

Problem

I am looking to pull some columns (Col1 and 2) of a table and put in JSON format and also write some hardcoded JSON in each node, like this. { "col1":"xxxx", "col2":"xxxx", "hardcodedString":"xxxx", "hardcodedString":"xxxx", "hardcodedString":"xxxx", "hardcodedString":"xxxx", "hardcodedString":"xxxx"}, I found the following git script, it creates a SP that should generate JSON but when i executed as required i get 'Commands Completed Succesfully' Any ideas where the output is going or indeed if a better way to acheive my JSON? ``` create procedure [dbo].[GetJSON] ( @schema_name varchar(50), @table_name varchar(50), @registries_per_request smallint = null ) as begin if ( ( select count(*) from information_schema.tables where table_schema = @schema_name and 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_schema = @schema_name and table_name = @table_name open schemaCursor fetch next from schemaCursor into @columnNavigator select @counter = count(*) from information_schema.columns where table_schema = @schema_name and 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 [' + @schema_name + '].[' + @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 ```

Original source