Microsoft SQL Server comes with
bcp.exe, a command-line tool that uses the Bulk Copy Program API. It allows to export data from tables and queries, import data from files into tables and it generates format files.
Bcp comes with a lot of command line parameters. Find them all here. The tool is very convenient if you want a swift and simple way to import or – in my case – export data from a database and dump the information in a XML file. In my case, an XML file is desired, containing some filtered data, renewed every few hours. We’ll take the following approach:
- Prepare the T-SQL query and define the output (XML schema)
- Create a batch file calling
bcp.exewith the appropriate parameters
- Schedule the batch file on a dedicated server
Using SQL Server Management Studio or other tool, key is to prepare your SQL query in advance. Make sure the output of the query reflects exactly the data you expect. For simplicty reasons, we’ll continue with a simple
SELECT Name, Address, City FROM Customers in this blogpost. We want the XML result to be structured as follows:
<Address>1600 Pennsylvania Avenue NW</Address>
<City>Washington, DC 20500</City>
As the final output should be an XML file, we need to tweak the T-SQL query with XML statements and append
FOR XML PATH('Customer'), ROOT('Database'), ELEMENTS to format the data accordingly.
Now we’ll construct the command line and save it in a batch file
bcp.bat. In order to create the XML file, the parameters
-x (XML output) and
-w (wide character also known as ntext) are important.
bcp.exe "SELECT Name, Address, City FROM Customers FOR XML PATH('Customer'), ROOT('Database'), ELEMENTS" queryout C:\BCP.XML -x -w -S SQLSERVER\INSTANCE
Give it a try, execute the batch file. Now, if the query ran successfully, open the XML file with Internet Explorer and you’ll get… an error!
Open the XML file with a text editor such as Notepad++ and you’ll notice that
bcp.exe inserts CRLF characters at specific intervals. Before you start implementing a state-of-the-art solution to find and replace those, the trick is to simply add a
-r parameter and you’re set, no more phanthom CRLFs. In other words, the following final command line does the trick:
bcp.exe "SELECT Name, Address, City FROM Customers FOR XML PATH('Customer'), ROOT('Database'), ELEMENTS" queryout C:\BCP.XML -x -w -r -S SQLSERVER\INSTANCE
In order to refresh the data on a regulare basis, simply use the Windows Task Scheduler, point to the batch file and either run the task as a domain user with priviliges to read data from the table (don’t forget the
-T switch for trusted connections) or extend the command line to use a SQL Server account that has the same priviliges:
bcp.exe "SELECT Name, Address, City FROM Customers FOR XML PATH('Customer'), ROOT('Database'), ELEMENTS" queryout C:\BCP.XML -x -w -r -S SQLSERVER\INSTANCE -U SQL_USER -P SQL_PASSWORD