脚本源码：
复制代码 代码如下:
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]’