SQL Server中将查询结果转换为Json格式脚本分享
脚本源码：
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEPROCEDURE[dbo].[SerializeJSON](
@ParameterSQLASVARCHAR(MAX)
)
AS
BEGIN
DECLARE@SQLNVARCHAR(MAX)
DECLARE@XMLStringVARCHAR(MAX)
DECLARE@XMLXML
DECLARE@ParamlistNVARCHAR(1000)
SET@Paramlist=N'@XMLXMLOUTPUT'
SET@SQL='WITHPrepareTable(XMLString)'
SET@SQL=@SQL+'AS('
SET@SQL=@SQL+@ParameterSQL+'FORXMLRAW,TYPE,ELEMENTS'
SET@SQL=@SQL+')'
SET@SQL=@SQL+'SELECT@XML=[XMLString]FROM[PrepareTable]'
EXECsp_executesql@SQL,@Paramlist,@XML=@XMLOUTPUT
SET@XMLString=CAST(@XMLASVARCHAR(MAX))
DECLARE@JSONVARCHAR(MAX)
DECLARE@RowVARCHAR(MAX)
DECLARE@RowStartINT
DECLARE@RowEndINT
DECLARE@FieldStartINT
DECLARE@FieldEndINT
DECLARE@KEYVARCHAR(MAX)
DECLARE@ValueVARCHAR(MAX)
DECLARE@StartRootVARCHAR(100);SET@StartRoot='<row>'
DECLARE@EndRootVARCHAR(100);SET@EndRoot='</row>'
DECLARE@StartFieldVARCHAR(100);SET@StartField='<'
DECLARE@EndFieldVARCHAR(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+'{'
--foreachrow
SET@FieldStart=CharIndex(@StartField,@Row,0)
WHILE@FieldStart>0
BEGIN
--parsenodekey
SET@FieldStart=@FieldStart+Len(@StartField)
SET@FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
SET@KEY=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
SET@JSON=@JSON+'"'+@KEY+'":'
--parsenodevalue
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
IFLEN(@JSON)>0SET@JSON=SubString(@JSON,0,LEN(@JSON))
SET@JSON=@JSON+'},'
--/foreachrow
SET@RowStart=CharIndex(@StartRoot,@XMLString,@RowEnd)
END
IFLEN(@JSON)>0SET@JSON=SubString(@JSON,0,LEN(@JSON))
SET@JSON='['+@JSON+']'
SELECT@JSON
END
GO
使用方法：
EXEC[SerializeJSON]'SELECT*FROM[Employee_TBL]'