Just Code‎ > ‎

SQLCMD BATCH FILE - Export large XML files from SQL server using SQLCMD

posted Jul 7, 2011, 3:59 AM by Peter Henell   [ updated Jan 25, 2012, 5:32 AM ]
There is a problem when running scripts that output large amount of text in a row from SQLCMD. The data will be truncated if it is to big. This is especially the case when you are trying to export XML files.

The example below is prepared for running multiple exports on a single server. Just copy paste the second part of the script to run SQLCMD with different input files.

Create a .bat file and enter this to it:
@echo off

REM EDIT THESE

set serverName=production_server_1
set databaseName=CustomerDB

 
REM Second part begin here
set inputFile=EXPORT_Customers.sql set outputFile=Customers.xml SQLCMD -S %serverName% -d %databaseName% -o %outputFile% -h -1 -y 0 -E -i %inputFile%
Read about the options sent to SQLCMD on book online.

The input file should look like this:
The nocount will make sure that only the xml structure will be the result in the output file.

EXPORT_Customer.sql:
SET NOCOUNT ON
:XML ON

select * from customer Customer
for xml auto, root('Customers')
Comments