SQL Server表转json

我正在寻找拉表的某些列(Col1和2)并以JSON格式放置,并且还在每个节点中编写一些硬编码的JSON,就像这样。

{“ col1”:“ xxxx”,“ col2”:“ xxxx”,“ hardcodedString”:“ xxxx”,“

hardcodedString”:“ xxxx”,“ hardcodedString”:“ xxxx”,“ hardcodedString”:“

xxxx”,“ hardcodedString“:” xxxx“},

我发现以下git脚本,它创建了一个应该生成JSON的SP,但是当我按要求执行时,我得到“成功完成命令”

有什么想法可以输出结果,或者是否有更好的方法来实现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

回答:

我不会真正建议这样做,在应用程序层中有很多更好的方法可以做到这一点,但是以下内容避免了循环,并且比当前方法更冗长:

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

注意:我已经更改了您的两部分对象名称(@schema和@table)以仅接受完整的对象名称。

想法是基本上使用SQL-

Server中的XML扩展将表转换为XML,然后仅将开始标记替换为{ColumnName:并将结束标记替换为,。然后,它需要再进行两次替换,才能停止在每行的最后一列中添加右括号,并,从JSON字符串中删除最后一个。

以上是 SQL Server表转json 的全部内容, 来源链接: utcz.com/qa/422613.html

回到顶部