SQL Server中如何将数据导出为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、执行完成后,得到结果如下图:

    标签: