数据库,序列化数据为json字符串

create PROCEDURE [dbo].[usp_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) > 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 + ']'
	select  @JSON
END

转载于:https://www.cnblogs.com/dfyg-xiaoxiao/p/7213737.html