create procedure[dbo].[wxsp_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]’