Script Source:
Copy Code code 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 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
How to use:
Copy Code code as follows:
Exec[serializejson] ' select*from[employee_tbl] '
