Writing each Row of a Dataset into XML files

[SheSaidImaPregy]

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
  Try
    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
      Loop
    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"
    Next
  Catch ex As SqlException
    ErrMail("Database Exception - DBtoXML Conversion", (ex).ToString())
    Return False
  End Try
End Function

[ptaylor965]

Hi,
Try

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
            dtXML.Rows.Clear()
            
            '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
            dtXML.Rows.Add(dtXMLRow)

            'Write XML file
            dtXML.WriteXml(XMLFilename)
        Next

[SheSaidImaPregy]

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

[ptaylor965]

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

[SheSaidImaPregy]

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!

[SheSaidImaPregy]

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?