您的位置:首页 > 数据库 > 数据库管理

SQL Server中如何将数据导出为XML或Json文件

更多 2015/12/29 来源:数据库管理学习浏览量:1197
学习标签: SQL Server
本文导读:怎样把SQLServer里的某个表的数据导出成XML文件或者Json文件呢?在应用程序中实现的方式很多,下面介绍通过SQL Server工具将查询出来的数据导出为XML或Json文件的方法

一、数据导出为XML文件

在SQL Server 2005之后提供了一个for xml子句在关系数据库中原生支持XML。通过该命令可以将二维关系结果集转换为XML,通过BCP就可以将数据存为XML了。

例如

1、sqladmin表的数据如图

 

2、通过如下BCP命令将其导出为XML文件

 

 
SQL 代码   复制

BCP "SELECT TOP 30 [bom_no],[LEVEL] FROM [sqladmin].[dbo].[bom] FOR XML path,TYPE, ELEMENTS ,ROOT('RegionSales')" QUERYOUT "d:\temp\test.XML" -c -t -T -S localhost

 

3、执行完成后查看Test.XML文件,如下图所示。

 

二、数据导出为Json文件

 

1、新建如下存储过程,例如命名为SerializeJSON

 

 
SQL 代码   复制

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

 

2、通过如下BCP命令将其导出为Json文件

 

 
SQL 代码   复制

BCP "[SerializeJSON]'select top 5 [bom_no],[LEVEL] FROM [sqladmin].[dbo].[bom]'" Queryout "d:\temp\test.json" -c -t -T -S localhost

 

3、执行完成后,得到结果如下图:

 

收藏
264
很赞
487