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.
SET NOCOUNT ON:XML ONselect * from customer Customer for xml auto, root('Customers')