Exporting data from SQL to XML

Written by Ingmar Verheij on May 8th, 2010. Posted in Batch

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

Empty variables during FOR loops in batch files

Written by Ingmar Verheij on April 27th, 2010. Posted in Batch

Batch files (*.bat / *.cmd) are easy to build and yet powerful scripts .
Although time is catching up and batch files are more and more replaced by vbScript and PowerShell, there are occasions you need them.
Batch files are especially powerful when you’re using (for) loops and variables. But the combination of those two is somewhat of a problem. Why? It doesn’t (seem to) work…
Let’s take the following situation.
You want to build a script that searches for files with a given extension in a given directory. You then strip the extension of the filename and process the file. This script would look like this:

Donate