CODE - TSQL convert Query to JSON

最新推荐文章于 2021-02-01 18:14:20 发布

weixin_33704591

最新推荐文章于 2021-02-01 18:14:20 发布

阅读量94

点赞数

文章标签： json 数据库

原文 ODE - TSQL convert Query to JSON

TSQL - Query to JSON

It is my philosophy that good development starts with the data. I have always stressed whenever possible allow your data processing to take place on your SQL server or database processing engine and rendering of the data to the application control engine. By the time your application server receives the data it should be in the truncated, filtered, limited by rows, converted to correct formats, free of whitespace ect. Your application should only receive what it will use on the screen and nothing more. This however requires a developer to actually develop code, Stored Procedures and Functions.

This follows the same logical philosophy and creates a simple Query to JSON procedure.

 
         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