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 = 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,@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,@FieldStart)
SET @KEY=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
SET @JSON=@JSON+'”
— parse node value
SET @FieldStart=@FieldEnd+1
SET @FieldEnd=CharIndex(‘ SET @Value=SubString(@Row,@FieldEnd-@FieldStart)
SET @JSON=@JSON+'”

SET @FieldStart=@FieldStart+Len(@StartField)
SET @FieldEnd=CharIndex(@EndField,@FieldStart)
SET @FieldStart=CharIndex(@StartField,@FieldEnd)
END
IF LEN(@JSON)>0SET @JSON=SubString(@JSON,LEN(@JSON))
SET @JSON=@JSON+’},’
–/ for each row

SET @RowStart=CharIndex(@StartRoot,@RowEnd)
END
IF LEN(@JSON)>0SET @JSON=SubString(@JSON,LEN(@JSON))
SET @JSON='[‘+@JSON+’]’
SELECT @JSON

END
GO

使用方法:
代码如下:
EXEC[SerializeJSON]’SELECT*FROM[Employee_TBL]’

dawei

【声明】:丽水站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

相关文章