Generating JSON from TSQL Query

Generating JSON from TSQL Query 2016年7月3日不静之心数据库

原文地址:http://www.firozansari.com/2008/11/21/generating-json-from-tsql-query/
其他参考:http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

CREATE PROCEDURE [dbo].[GetJSON] (

@ParameterSQL AS VARCHAR(MAX)

)

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)>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

—->

EXEC GetJSON 'SELECT * FROM dbo.Employee_TBL'

[{"UserId":"7C92EB27-DD81-498E-82CE-18192C940328","FirstName":"Bill","LastName":"Gates","Age":"45","LastLogin":"2008-11-02T00:00:00"},{"UserId":"58E75687-8D0F-423A-8A76-4B1D750F62FD","FirstName":"Steve","LastName":"Job","Age":"52","LastLogin":"2008-10-17T00:00:00"}]