SQL Server中将查询结果转换为Json格式脚本分享

脚本源码：

set ansi_nulls on
go
set quoted_identifier on
go
create procedure[dbo].[serializejson](
@parametersql as varchar(max)
)
as
begin
  
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))
  
declare @json varchar(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)
        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+'",'
  
        set @fieldstart=@fieldstart+len(@startfield)
        set @fieldend=charindex(@endfield,@row,@fieldstart)
        set @fieldstart=charindex(@startfield,@row,@fieldend)
    end   
    if len(@json)>0set @json=substring(@json,0,len(@json))
    set @json=@json+'},'
    --/ for each row
  
    set @rowstart=charindex(@startroot,@xmlstring,@rowend)
end
if len(@json)>0set @json=substring(@json,0,len(@json))
set @json='['+@json+']'
select @json
  
end
go

使用方法：

exec[serializejson]'select*from[employee_tbl]'