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?
SQL Server – Export Tables to XML Files
exportsql-server-2008xml
Related Question
- Sql-server – SQL Server XML Insert Optimize
- SQL Server 2008 Configuration Comparison – How to Compare Two Configurations
- Generate XML using Oracle XML schema
- Sql-server – How to shred .docx XML
- SQL Server – How to Export Database from SQL Server 2000 to 2012
- SQL Server to XML – Alternatives for Exporting Files
- SQL Server 2012 – How to Export Tables
- Sql-server – Import XML files into SQL Server 2012
Best Answer
This job can be done using the BCP utility.
Pre-requisites:
You would need to configure your server, to enable
xp_cmdshell
:Permissions, which account
xp_cmdshell
is running as?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.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: