mysql查询序列化json格式_数据库,序列化数据为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 = ''

declare @EndRoot varchar(100);

set @EndRoot = ''

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