CODE - TSQL convert Query to 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.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77 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
Call thestored procedure
?
1 EXEC[SerializeJSON]'SELECT*FROM[Employee_TBL]'