经过大量搜索并拼凑出使用Web上的FOR
XML和.nodes()命令转换结果集的非常出色的技术后,我能够创建一个合理的单一查询(而不是存储过程)很好地将任意SQL查询转换为
JSON数组.
该查询将每个数据行编码为具有前导逗号的单个JSON对象.
数据行用括号括起来,然后整个结果集将被导出到文件中.
这是带有示例表的查询：
declare @xd table (col1 varchar(max),col2 int,col3 real,colNull int)
insert into @xd
select '',null,null
UNION ALL select 'ItemA',123,123.123,null
UNION ALL select 'ItemB',456,456.456,null
UNION ALL select '7890',789,789.789,null
select '[{}'
UNION ALL
select ',{' + STUFF((
(select ','
+ '"' + r.value('local-name(.)','varchar(max)') + '":'
+ case when r.value('./@xsi:nil','varchar(max)') = 'true' then 'null'
when isnumeric(r.value('.','varchar(max)')) = 1
then r.value('.','varchar(max)')
else '"' + r.value('.','varchar(max)') + '"'
end
from rows.nodes('/row/*') as x(r) for xml path(''))
),1,'') + '}'
from (
-- Arbitrary query goes here,(fields go where t.* is,table where @xd t is)
select (select t.* for xml raw,type,elements XSINIL) rows
from @xd t
) xd
UNION ALL
select ']'
我对它的最大批评是,它的速度非常慢.
目前大约需要3:30,大约42,000行.
我的另一个大批评是,它目前假设看起来像数字的所有东西都是数字.它不会尝试至少发现列类型(我甚至不确定它是否可以).
最后一个小批评是第一个数据行预先有一个逗号,技术上它不应该.为了弥补这一点,它需要在第一行中启动JSON数组的空JSON对象.
邀请的其他批评(最好是解决方案),我唯一真正的限制是解决方案在许多任意SQL查询上都可以很好地重复,而不必明确地识别列名.
我正在使用sql Server 2012.
谢谢,还有像我一样正在寻找广义sql结果的其他人 – > JSON阵列转换器,享受！
解决方法
我说如果你真的想要提升表现,可以使用元编程.下面的示例尝试使用40,000行并在不到一秒的时间内返回结果(不计入插入最初的40k行,在此示例中仅需要大约2秒).它还会考虑您的数据类型,以便不将数字括在引号中.
declare @xd table (col1 varchar(max),colDate datetime,colNull int);
declare @i int = 0;
while @i < 10000 begin
set @i += 1;
insert into @xd
select '',null
union all select 'ItemA',getDate(),null
union all select 'ItemB',null
union all select '7890',null;
end;
select *
into #json_base
from (
-- Insert sql Statement here
select * from @xd
) t;
declare @columns table (
id int identity primary key,name sysname,datatype sysname,is_number bit,is_date bit);
insert into @columns(name,datatype,is_number,is_date)
select columns.name,types.name,case when number_types.name is not NULL
then 1 else 0
end as is_number,case when date_types.name is not NULL
then 1 else 0
end as is_date
from tempdb.sys.columns
join tempdb.sys.types
on (columns.system_type_id = types.system_type_id)
left join (values ('int'),('real'),('numeric'),('decimal'),('bigint'),('tinyint')) as number_types(name)
on (types.name = number_types.name)
left join (values ('date'),('datetime'),('datetime2'),('smalldatetime'),('time'),('datetimeoffset')) as date_types(name)
on (types.name = date_types.name)
where object_id = OBJECT_ID('tempdb..#json_base');
declare @field_list varchar(max) = STUFF((
select '+'',''+' + QUOTENAME(QUOTENAME(name,'"') + ':','''')
+ '+' + case when is_number = 1
then 'COALESCE(LTRIM('
+ QUOTENAME(name) + '),''null'')'
when is_date = 1
then 'COALESCE(QUOTENAME(LTRIM(convert(varchar(max),'
+ QUOTENAME(name) + ',126)),''"''),''null'')'
else 'COALESCE(QUOTENAME('
+ QUOTENAME(name) + ',''null'')'
end
from @columns
for xml path('')),5,'');
create table #json_result (
id int identity primary key,line varchar(max));
declare @sql varchar(max) = REPLACE(
'insert into #json_result '
+ 'select '',{''+{f}+''}'' '
+ 'from #json_base','{f}',@field_list);
exec(@sql);
update #json_result
set line = STUFF(line,'')
where id = 1;
select '['
UNION ALL
select line
from #json_result
UNION ALL
select ']';
drop table #json_base;
drop table #json_result;
