Exporting data from SQL to XML

Exporting data from a SQL database to a XML file is very easy, if you know how to.
Using the bcp command (bulk copy program), a tool supplied by Microsoft, you can easily export all the data you want.

– Create a new text file and type the following text:

<root>
</root>

– Save the text file with a .xml extension, for instance data.xml;
– Open a new Command Prompt (as Administrator in Vista/Win7/2008 R2);
– Execute the following command : bcp.exe “SELECT * FROM Table AS XML RAW” queryout data.xml -c -r -t -T.

If you want to script the process with batch (we’re a big fan of batch files) you can use the following script:

@Echo Off

REM Create an empty XML file
ECHO ^<root^> >data.xml
ECHO ^</root^> >>data.xml

REM Export the data using BCP
bcp.exe “SELECT * FROM Table AS XML RAW” queryout data.xml -c -r -t -T.

Ingmar Verheij & Daniel Nikolic