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

页面导航:首页 > 数据库 > MsSql > SQL Server中将查询结果转换为Json格式脚本分享

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

来源:互联网 作者:脚本宝典 时间:2015-06-05 13:15 【

这篇文章主要介绍了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, @[email protected] 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 @[email protected]+Len(@StartRoot)
    SET @RowEnd=CharIndex(@EndRoot,@XMLString,@RowStart)
    SET @Row=SubString(@XMLString,@RowStart,@[email protected])
    SET @[email protected]+'{'
  
    -- for each row
    SET @FieldStart=CharIndex(@StartField,@Row,0)
    WHILE @FieldStart>0
    BEGIN
        -- parse node key
        SET @[email protected]+Len(@StartField)
        SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
        SET @KEY=SubString(@Row,@FieldStart,@[email protected])
        SET @[email protected]+'"'[email protected]+'":'
        -- parse node value
        SET @[email protected]+1
        SET @FieldEnd=CharIndex('</',@Row,@FieldStart)
        SET @Value=SubString(@Row,@FieldStart,@[email protected])
        SET @[email protected]+'"'[email protected]+'",'
  
        SET @[email protected]+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 @[email protected]+'},'
    --/ for each row
  
    SET @RowStart=CharIndex(@StartRoot,@XMLString,@RowEnd)
END
IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
SET @JSON='['[email protected]+']'
SELECT @JSON
  
END
GO

 

使用方法:

复制代码代码如下:

EXEC[SerializeJSON]'SELECT*FROM[Employee_TBL]'
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<