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

Post date: Jul 7, 2011 10:59:11 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 offREM EDIT THESEset serverName=production_server_1set 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 ONselect * from customer Customer for xml auto, root('Customers')