SQL Server - export tables to XML files

Question

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?

Answer 1

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;

• r0tt: 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: The tables related to erros seem to have binary data. I excluded them and will take care of them separatly.
• McNets: AFAK you should use a format file to export binary data.