執(zhí)行下面的存儲過程：

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 執(zhí)行下面的腳本就能生成JSON格式數(shù)據(jù) EXEC[SerializeJSON] 'SELECT * FROM [dbo].[TEST]'
?

轉(zhuǎn)載于:https://www.cnblogs.com/hank-chen/p/5916929.html

總結(jié)

以上是生活随笔為你收集整理的SQL SERVER 数据导出JSON的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。