欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-11-25 15:22:10
脚本源码: 复制代码 代码如下: set ansi_nulls on go set quoted_identifier on go create procedu...

脚本源码:

复制代码 代码如下:

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]'