1、先写个存储过程: CREATE PROCEDURE [dbo].[SerializeJSON]( @ParameterSQL AS VARCHAR(MAX) ) AS BEGIN DECLARE @SQL NVARCHAR(MAX) DECLARE @XMLString NVARCHAR(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 nvarchar(MAX)) DECLARE @JSON nVARCHAR(MAX) DECLARE @Row NVARCHAR(MAX) DECLARE @RowStart INT DECLARE @RowEnd INT DECLARE @FieldStart INT DECLARE @FieldEnd INT DECLARE @KEY NVARCHAR(MAX) DECLARE @Value NVARCHAR(MAX) DECLARE @StartRoot NVARCHAR(100);SET @StartRoot='<row>' DECLARE @EndRoot NVARCHAR(100);SET @EndRoot='</row>' DECLARE @StartField NVARCHAR(100);SET @StartField='<' DECLARE @EndField NVARCHAR(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 2、然后执行 EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO EXEC xp_cmdshell 'bcp "EXEC [Xxx.Module].dbo.SerializeJSON ''SELECT * FROM SysParameter''" queryout D:\data.json -c -q -T -k' --执行存储过程必须是完整的连接,否则会提示找不到存储过程 EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 0 GO RECONFIGURE GO 注:SQLSERVER2016及以上版本可以使用SELECT * FROM SysParameter FOR JSON PATH来生成Json数据