sqlrowset 转化为json_将SQL Server表数据，转换为JSON格式

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=''DECLARE @EndRoot VARCHAR(100);SET @EndRoot=''DECLARE @StartField VARCHAR(100);SET @StartField=''SET @RowStart=CharIndex(@StartRoot,@XMLString,0)

SET @JSON=''WHILE @RowStart>0BEGIN

SET @RowStart=@RowStart+Len(@StartRoot)

SET @RowEnd=CharIndex(@EndRoot,@XMLString,@RowStart)

SET @Row=SubString(@XMLString,@RowStart,@RowEnd-@RowStart)

SET @JSON=@JSON+'{'

-- foreach row

SET @FieldStart=CharIndex(@StartField,@Row,0)

WHILE @FieldStart>0BEGIN--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+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)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))

SET @JSON=@JSON+'},'

--/ foreach 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