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数据