locked
Export XML data to File RRS feed

  • Question

  • I have seen other questions like this, but none that provide the answer I need.

    I need to move data from one SQL DB to another, located in different offices.

    I want to do this programmatically as part of an application, ether using VB or T-SQL.

    I understan how to use FOR XML to get the data into an XML column.

    How do I save that to a file that I can move to a different machine?

    I would prefer not to use bcp, so that if I do it in a stored proc, I do not have to use xp_cmdshell.

    Thank you.

    Richard

    Sunday, June 18, 2006 6:40 PM

Answers

  • Hi Richard,

    We dont have any tools that would allow you to export a table to XML and then have another table automatically consume it. There are a few ways to achieve this though. Here are a couple:

    Option 1) You can generate a FOR XML statement for your source table. Save the results to an XML file, and then on the destination database you the XML Datatype's nodes() method to convert the xml into relational data, and insert it into your destination table. (Here is some documentation on the nodes() function. http://msdn2.microsoft.com/en-us/ms188282.aspx)

    Option 2) You could fill a dataset with the relational data that you are interested in. Serialize the dataset as XML. Rehydarate another dataset with the xml and insert it into your destination tables.

    Option 1 can be done using just T-SQL within SP's. Option 2 requires the use of .Net, but you could do it within an SP using SQLCLR in the engine.

     

     

    Monday, June 19, 2006 8:54 PM

All replies

  • Hi Richard,

    We dont have any tools that would allow you to export a table to XML and then have another table automatically consume it. There are a few ways to achieve this though. Here are a couple:

    Option 1) You can generate a FOR XML statement for your source table. Save the results to an XML file, and then on the destination database you the XML Datatype's nodes() method to convert the xml into relational data, and insert it into your destination table. (Here is some documentation on the nodes() function. http://msdn2.microsoft.com/en-us/ms188282.aspx)

    Option 2) You could fill a dataset with the relational data that you are interested in. Serialize the dataset as XML. Rehydarate another dataset with the xml and insert it into your destination tables.

    Option 1 can be done using just T-SQL within SP's. Option 2 requires the use of .Net, but you could do it within an SP using SQLCLR in the engine.

     

     

    Monday, June 19, 2006 8:54 PM
  • Here is code to write XML as destination. Variable uvRecordSet was created by Recordset Destination

     

    Public Sub Main()

    Dim adp As New OleDb.OleDbDataAdapter

    Dim dt As New DataTable("WriteXMLTableName")

    adp.Fill(dt, Dts.Variables("uvRecordSet").Value)

    dt.WriteXml("C:\myxml.xml", False)

    Dts.TaskResult = Dts.Results.Success

    End Sub

    Monday, October 16, 2006 10:13 AM