将select 转为json
CREATE PROCEDURE[dbo].[WXSP_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]'
最新文章
- ASPNET 导出EXCEL表
- 学习图像算法阶段性总结 (附一键修图Demo) 2016.04.19更新demo
- python异步爬虫
- 07 concurrency and Multi-version
- Java同步块
- Ruby设计模式透析之 —— 适配器(Adapter)
- C# 异步和委托学习
- html5 中的SVG 和canvas
- CKEdit( htm编辑器)
- firewall服务配置
- python笔记18-高阶函数
- SQA
- 微软Azure AspNetCore微服务实战 第一期
- 【BZOJ3309】DZY Loves Math（莫比乌斯反演）
- DataGuard---->备库参数db_file_name_convert和log_file_name_convert的作用
- Ajax的课外了解
- IntelliJ IDEA 启动tomcat服务器报Error running 'Unnamed': Address localhost:1099 is already in use错误的问题
- Java Decompiler Plugin For Eclipse IDE
- Get started with ros -- 1
- 一个窗口里包含一个iframe，点击iframe内的submit按钮，返回的视图总是显示在iframe中，我想要的效果是点击按钮后返回的视图是在浏览器窗口中...?asp.net mvc 的action中，不用js怎么实现??????????