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