SqlServer将查询结果转换为XML和JSON

很久之前用到的，现在整理在这，里面一些代码来源于网上，不过有些bug已被我修改了。

1.查询结果转xml

declare @parametersql nvarchar(max)='select * from table';
declare @sql nvarchar(max)
declare @xmlstring varchar(max)
declare @xml xml
declare @paramlist nvarchar(1000)
set @paramlist = n'@xml xml output'
set @sql = 'with preparetable (xmlstring)'
set @sql = @sql + 'as( '
set @sql = @sql + @parametersql+ ' for xml raw,type,elements'
set @sql = @sql + ')'
set @sql = @sql + 'select @xml=[xmlstring]from[preparetable]'
exec sp_executesql @sql, @paramlist, @xml=@xml output
set @xmlstring=cast(@xml as varchar(max))
select @xml;
select @xmlstring;

其中@parametersql为要查询的语句，@xmlxml格式数据，@xmlstringxml转成字符串

2.查询结果转json

查询结果转换成json需要经过两个步骤，首先将查询结果转成xml数据，然后通过xml数据转成json

xml转json的存储过程如下：

create procedure [dbo].[serializejson] (
   @xml xml,
   @json_xml nvarchar(max) output
  )
as
begin
  declare @xmlstring nvarchar(max);
  set @xmlstring = cast(@xml as nvarchar(max));
  begin try--//开始捕捉异常
    declare @json nvarchar(max);
    declare @row varchar(max);
    declare @rowstart int;
    declare @rowend int;
    declare @fieldstart int;
    declare @fieldend int;
    declare @key varchar(max);
    declare @value varchar(max);
    declare @startroot varchar(100);
    set @startroot = '<row>';
    declare @endroot varchar(100);
    set @endroot = '</row>';
    declare @startfield varchar(100);
    set @startfield = '<';
    declare @endfield varchar(100);
    set @endfield = '>';
    set @rowstart = charindex(@startroot, @xmlstring, 0);
    set @json = '';
    while @rowstart>0
    begin
      set @rowstart = @rowstart + len(@startroot);
      set @rowend = charindex(@endroot, @xmlstring, @rowstart);
      set @row = substring(@xmlstring, @rowstart, @rowend - @rowstart);
      set @json = @json + '{';
      --//for each row
      set @fieldstart = charindex(@startfield, @row, 0);
      while @fieldstart>0
      begin
        --//parse node key
        set @fieldstart = @fieldstart + len(@startfield);
        set @fieldend = charindex(@endfield, @row, @fieldstart);
        declare @end int = charindex('/>', @row, @fieldstart);
        if @end<1
          or @end>@fieldend
        begin
          set @key = substring(@row, @fieldstart, @fieldend - @fieldstart);
          set @json = @json + '"' + @key + '":';
          --//parse node value
          set @fieldstart = @fieldend + 1;
          set @fieldend = charindex('</', @row, @fieldstart);
          set @value = substring(@row, @fieldstart, @fieldend - @fieldstart);
          set @json = @json + '"' + @value + '",';
        end;
        set @fieldstart = @fieldstart + len(@startfield);
        set @fieldend = charindex(@endfield, @row, @fieldstart);
        set @fieldstart = charindex(@startfield, @row, @fieldend);
      end;
      if len(@json)>0
        set @json = substring(@json, 0, len(@json));
      set @json = @json + '},';
      --// for each row
      set @rowstart = charindex(@startroot, @xmlstring, @rowend);
    end;
    if len(@json)>0
      set @json = substring(@json, 0, len(@json));
    --//set @json = '[' + @json + ']';
    set @json_xml = @json;
  end try--//结束捕捉异常
  begin catch--//有异常被捕获
    set @json_xml = @xmlstring;
  end catch;--//结束异常处理
end;

以上所述是小编给大家介绍的sqlserver将查询结果转换为xml和json，希望对大家有所帮助