缥帙各舒散，前后互相逾。这篇文章主要讲述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=https://www.songbingjia.com/android/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
--使用: EXEC [SerializeJSON] SELECT * FROM [xxx]
END
GO
推荐阅读
- JavaScript 自动获取所有a标签，并新标签打开
- Linux777权限是啥
- JavaScript 获取当前周数
- sequelize.query 原始查询
- Mybatis-----查询结果合并成集合
- SQLServer tempdb 数据库文件迁移到D盘
- css3 监控横屏，内容旋转90度
- 如何查看请求回来的JSON数据
- 如何设置导出excel的格式