Post reply

Save XML records to file

  • August 28, 2008 at 1:13 am

    #94506

    Hi guys,

    I have a table where one field is an xml data type. Can anyone please guide me how to save the xml record to a physical file on disk?

    I know you can open the file and manually save the file to disk but if this can be done programatically would be ideal solution..

    Please post your ideas.

    Thanks

  • August 28, 2008 at 9:15 am

    #864502

    Hi,

    there are so many ways to do this. You could use SSIS, DTS, bcp, c#,VB .... the list goes on.

    You need to specify how big the operation is. Are you creating one file a day or one file a second?

    I used c# to export a few thousand pdfs from a database into real pdf files in about 45 seconds. If it is raw exporting I would go for bcp, as it is one of the fastest ways of doing that sort of thing.

    Regards

    GermanDBA

    Regards,

    WilliamD

  • August 29, 2008 at 2:24 am

    #864877

    Thanks for your reply!

    I need to generate the xml file once or twice a day. I prefer to try using C# to do this task.

    Can you please guide me through the implemention in c#?

    Thanks

  • August 31, 2008 at 11:45 pm

    #865555

    Hi Jonathan,

    I am by no means a profesional c# programmer. I hacked together a very quick and ugly tool to export the files into the filesystem. I would suggest you take a look at somewhere like www.codeproject.com.

    You can find a vast array of programming examples and tutorials that should help you do this exact export. I would say you can best look for SqlDataReader and StreamWriter to achieve what you are planning to do.

    May I reiterate that the bcp tool would be even easier to use if you are creating one or two files a day. You would write your select statement to get your data from the server and run bcp using it, writing the output to an empty file. It goes something like this:

    bcp "select col1,col2,col3 FROM DATABASE.DBO.XMLTABLE " queryout "c:\export.xml" -T -c -C RAW -t'

    This command is run from the command line and uses the first section between inverted commas as the sql statement to be run, then behind queryout is the export location (including filename), then come a couple of extra commands saying how the data is to be handled. For the full explanation, please see BOL (just typ bcp in your sql query window, highlight it and hit F1 for help).

    If I can help you at all, just show me what you have so far and where you need help. I will help as far as I can.

    Regards

    GermanDBA

    Regards,

    WilliamD

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply