SQL Server中将查询结果转换为Json格式脚本分享

各位用户为了找寻关于SQL Server中将查询结果转换为Json格式脚本分享的资料费劲了很多周折。这里教程网为您整理了关于SQL Server中将查询结果转换为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='<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]'