SQL Server converts query results to script sharing in Json format, serverjson

SQL Server converts query results to script sharing in Json format, serverjson

Script source code:

Copy codeThe Code is as follows:
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
DECLARE @ Paramlist NVARCHAR (1000)
SET @ Paramlist = n' @ 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)> 0 SET @ JSON = SubString (@ JSON, 0, LEN (@ JSON ))
SET @ JSON = @ JSON + '},'
--/For each row

SET @ RowStart = CharIndex (@ StartRoot, @ XMLString, @ RowEnd)
END
If len (@ JSON)> 0 SET @ JSON = SubString (@ JSON, 0, LEN (@ JSON ))
SET @ JSON = '[' + @ JSON + ']'
SELECT @ JSON

END
GO

Usage:
Copy codeThe Code is as follows:
EXEC [SerializeJSON] 'select * FROM [Employee_TBL]'