Original Ode-tsql convert Query to JSON
Tsql-query to JSON
It is my philosophy, good development starts with the data. I have all stressed whenever possible allow your data processing to take place on your SQL Server or database Processin G engine and rendering of the data to the application control engine. By the time your application server receives the data it should is in the truncated, filtered, limited by rows, converted To correct formats, free of whitespace ect. Your application should only receive what it'll use on the screens 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.
?
| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465 666768697071727374757677 |
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE[dbo].[SerializeJSON](@ParameterSQL AS VARCHAR(MAX))ASBEGIN DECLARE @SQL NVARCHAR(MAX)DECLARE @XMLString VARCHAR(MAX)DECLARE @XML XMLDECLARE @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] OUTPUTSET @XMLString=CAST(@XML AS VARCHAR(MAX)) DECLARE @JSON VARCHAR(MAX)DECLARE @Row VARCHAR(MAX)DECLARE @RowStart INTDECLARE @RowEnd INTDECLARE @FieldStart INTDECLARE @FieldEnd INTDECLARE @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>0BEGIN 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)ENDIF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))SET @JSON=‘[‘[email protected]+‘]‘SELECT @JSON ENDGO |
Call thestored procedure
?
| 1 |
EXEC[SerializeJSON]‘SELECT*FROM[Employee_TBL]‘ |
Code-tsql convert Query to JSON
