SQL Server - export tables to XML files 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?
This job can be done using the BCP utility. 

Pre-requisites:


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

Permissions, which account xp_cmdshell is running as?  

xp_cmdshell 'whoami' 

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 &lt;...&gt; values according to your system configuration:

use &lt;your_database&gt;
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 = '&lt;server\instance&gt;';
    set @BcpParams = '-t -T -w';
    set @RootFolder = '&lt;folder_name&gt;';


    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 &lt;&gt; 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;