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

来源：互联网发布：淘宝秋季女装编辑：程序博客网时间：2024/06/07 10:09
create PROCEDURE [dbo].[usp_SerializeJSON]@ParameterSQL as varchar(max)ASBEGINdeclare @SQL nvarchar(max)declare @XMLString varchar(max)declare @XML xmldeclare @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 outputset @XMLString = cast(@XML as varchar(max))declare @JSON varchar(max)declare @Row varchar(max)declare @RowStart intdeclare @RowEnd intdeclare @FieldStart intdeclare @FieldEnd intdeclare @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 beginset @RowStart = @RowStart + len(@StartRoot)set @RowEnd = charindex(@EndRoot, @XMLString, @RowStart)set @Row = substring(@XMLString, @RowStart,@RowEnd - @RowStart)set @JSON = @JSON + '{'-- for each rowset @FieldStart = charindex(@StartField, @Row, 0)while @FieldStart > 0 begin-- parse node keyset @FieldStart = @FieldStart + len(@StartField)set @FieldEnd = charindex(@EndField, @Row, @FieldStart)set @KEY = substring(@Row, @FieldStart,@FieldEnd - @FieldStart)set @JSON = @JSON + '"' + @KEY + '":'-- parse node valueset @FieldStart = @FieldEnd + 1set @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 rowset @RowStart = charindex(@StartRoot, @XMLString, @RowEnd)endif len(@JSON) > 0 set @JSON = substring(@JSON, 0, len(@JSON))set @JSON = '[' + @JSON + ']'select  @JSONEND
0 0