0

I need to export all tables (500) of a sql server 2008 R2 database to XML. What is the best and fastest way to do that?

1 Answer 1

2

This job can be done using the BCP utility.

Pre-requisites:

  1. You would need to configure your server, to enable xp_cmdshell:

    EXEC master.dbo.sp_configure 'show advanced options', 1
    RECONFIGURE
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1
    RECONFIGURE
    
  2. Permissions, which account xp_cmdshell is running as?

    xp_cmdshell 'whoami' 
    
  3. Create a new folder, where you will save the files, and grant Full access on this folder to the user of step 2.

I recommend you first try with a few tables, add a TOP criteria to the cursor selection, just to verify this script runs well in your server.

select TOP 3 TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.Tables 
where TABLE_TYPE = 'BASE TABLE';

Basically the script gets a list of tables from INFORMATION_SCHEMA, and executes the BCP utility for each element in the list.

XML files will be named as: DATABASE_SCHEMA_TABLE.XML

This is the full script, before to run it, replace the enclosed <...> values according to your system configuration:

use <your_database>
go

declare @ServerInstance nvarchar(50),
        @Database sysname, 
        @Schema sysname,
        @Table sysname, 
        @RootFolder nvarchar(165),
        @BcpParams nvarchar(100),
        @cmdBCP nvarchar(500),
        @FQI varchar(600),
        @FileName varchar(600),
        @retExec int;

    set @ServerInstance = '<server\instance>';
    set @BcpParams = '-t -T -w';
    set @RootFolder = '<folder_name>';


    declare curXml cursor fast_forward for
        select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
        from INFORMATION_SCHEMA.Tables 
        where TABLE_TYPE = 'BASE TABLE';

    open curXml;
    fetch next from curXml into @Database, @Schema, @Table

    while @@FETCH_STATUS = 0
    begin

        set @FQI = @Database + '.' + @Schema + '.' + @Table;
        set @FileName = @RootFolder + @Database + '_' + @Schema + '_' + @Table + '.xml';

        select @cmdBCP = ' bcp "SELECT * FROM '
                         + @FQI 
                         + ' row FOR XML AUTO, ROOT(''' + @Table + '''), elements"'
                         + ' queryout "' + @FileName + '" '
                         + @BcpParams
                         + ' -S ' + @ServerInstance;

        print @cmdBCP;

        EXEC @retExec = xp_cmdshell @cmdBCP;

        if @retExec <> 0
        begin
            close curXml;
            deallocate curXml;
            raiserror('BCP Error', 16, 1);
        end

        fetch next from curXml into @Database, @Schema, @Table;
    end

    close curXml;
    deallocate curXml;
3
  • It seem to work for some tables - I run into errors for some other tables:. Msg 50000, Level 16, State 1, Line 47 BCP Error Msg 16916, Level 16, State 1, Line 50 A cursor with the name 'curXml' does not exist. Msg 16916, Level 16, State 1, Line 53 - Is this related to constrain problems?
    – r0tt
    Jan 2, 2017 at 9:03
  • The tables related to erros seem to have binary data. I excluded them and will take care of them separatly.
    – r0tt
    Jan 2, 2017 at 9:50
  • AFAK you should use a format file to export binary data.
    – McNets
    Jan 2, 2017 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.