I need to write each row of a daset to a separate xml file, which will be named based upon the ID of the current row.

I am very new to XML, as I have never believed in it, which meant it was crap. Anyway, I am trying to merge XML into my programming, and need a bit of help.

What I need to do is select all records met by a certain criteria in the database, store it in a dataset, iterate through each row in the dataset, write the XML file according to each row, then save it in the corresponding directory, also based upon a value in the current row (this part isn't important, but I might use it).

I need this to be very efficient, as it will be dealing with 500-2000 records a day. Therefore I need it to be quick, safe, and effective. Any suggestions will help. Thank you.

This is what I have so far:

Public Function DBtoXML() As Boolean
    Dim ds As New DataSet()
    Dim da As New SqlDataAdapter("SELECT * FROM Stories WHERE DateCreated < (getdate() - 7)", conPubs)
    Dim filename As String = "DBtoXML-" & DateTime.Now.ToString("d") & ".xml"

    If File.Exists(Server.MapPath("/conversions/") & filename) Then
      Me.i = 0

      Do While Me.i < 100
        i += 1
        filename = "DBtoXML-" & DateTime.Now.ToString("d") & i & ".xml"

        If Not File.Exists(Server.MapPath("/conversions") & filename) Then Exit Loop

        If i = 100 Then ErrMail("DBtoXML File Name Failure", "Could not find a suitable filename for:  " & Server.MapPath("/conversions/") & filename) : Return False
    End If

    da.Fill(ds, "Stories")

    'Used for backups, incase of a failure or incase I need it later on.
    ds.WriteXml(Server.MapPath("/conversions/") & filename)

    For Me.i = 0 To da.Tables("Stories").Rows.Count - 1
    'I could also use, For Each dr As DataRow in da.Tables("Stories"), right? I'd prefer that.
      'I need to name the file after a row... called... "SID"
  Catch ex As SqlException
    ErrMail("Database Exception - DBtoXML Conversion", (ex).ToString())
    Return False
  End Try
End Function


Dim dtXML As New DataTable
        Dim dtData As New DataTable
        Dim dtRow As DataRow
        Dim dtXMLRow As DataRow
        Dim XMLFilename As String

        '==============Fill dtData from database============

        'Add matching columns to your XML datatable
        dtXML.Columns.Add("Column Name")
        dtXML.Columns.Add("Column Name2")

        For Each dtRow In SQLTable.Rows
            'Clear last row
            'Make new row
            dtXMLRow = dtXML.NewRow

            'Copy data from one Datarow to another
            dtXMLRow("Column Name") = dtRow("Column Name").ToString
            dtXMLRow("Column Name2") = dtRow("Column Name2").ToString

            'Add the new built row to the dtXML

            'Write XML file

Is there a way to do it without creating two instances of tables?

The only way i know how to do it with only one instance of a table would be to only download one record from your database at a time, into a DataTable and then do dt.WriteXML

This would require many more database connections but possibly less CPU Usage

Yeah I will keep that down to a minimum. I mean, what I could probably do is just read the data and write it as I go. I will look into it and let you know what I have found. But thank you!

Ok, I haven't tested my code, so I am not going to post it yet.

What I am doing:

Create a DataTable and DataAdapter
Fill the DataTable
Create a DataRow instance
For each datarow in datatable
use an xmlwriter to write each column

Sound good?