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

Ingmar Verheij

At the time Ingmar wrote this article he worked for PepperByte as a Senior Consultant (up to May 2014). His work consisted of designing, migrating and troubleshooting Microsoft and Citrix infrastructures. He was working with technologies like Microsoft RDS, user environment management and (performance) monitoring. Ingmar is User Group leader of the Dutch Citrix User Group (DuCUG). RES Software named Ingmar RES Software Valued Professional in 2014.

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Tags: , , ,

Comments (1)

  • Vaibhav
    13 July 2017 at 07:35 |

    Hi,

    Do you know how to import such xml files back into respective tables?

    I tried and it fails saying “Unexpected end of file”

Leave a comment

*

Donate

%d bloggers like this: