Member Avatar for 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
  • 2 Contributors
  • 5 Replies
  • 928 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by SheSaidImaPregy

Dani AI

Generated

Streaming rows with a forward-only reader and writing each row with an XmlWriter is the simplest, fastest and most memory-friendly pattern for this problem. It avoids filling a large DataSet or repeatedly cloning DataTables (the approach outlined), and follows the XmlWriter idea that started. Key safety items are: sanitize the ID for a filename, create the directory if missing, write to a temporary file and atomically rename it to avoid partial files, and always dispose connections/writers via Using blocks.

Recommended minimal workflow:

  • Open a single SqlConnection and execute the SELECT. Use CommandBehavior.SequentialAccess if columns are large.
  • Iterate the SqlDataReader row by row.
  • For each row: build a safe folder path (partition by date or category if many files), sanitize the SID for a filename, create the directory, write XML to a .tmp file using XmlWriter (UTF-8, optional Indent), close the writer, then move the temp file to the final .xml name.
  • Wrap DB, reader and writer in Using blocks and catch/log IO and SQL exceptions.

Example VB.NET pattern (adapt paths/SQL to the app):

' Stream rows to one XML file per row
Function ExportStoriesToXml(connString As String, outputRoot As String) As Boolean
    Try
        Directory.CreateDirectory(outputRoot)
        Using cn As New SqlConnection(connString)
            cn.Open()
            Using cmd As New SqlCommand("SELECT SID, Title, Body, Category FROM Stories WHERE DateCreated < DATEADD(day,-7,GETDATE())", cn)
                Using rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
                    While rdr.Read()
                        Dim sid = SanitizeFileName(rdr("SID").ToString())
                        Dim folder = Path.Combine(outputRoot, SanitizeFileName(rdr("Category").ToString()))
                        Directory.CreateDirectory(folder)
                        Dim finalFile = Path.Combine(folder, sid & ".xml")
                        Dim tmpFile = finalFile & ".tmp"
                        Using xw = XmlWriter.Create(tmpFile, New XmlWriterSettings() With {.Indent = True, .Encoding = Text.Encoding.UTF8})
                            xw.WriteStartDocument()
                            xw.WriteStartElement("Story")
                            xw.WriteElementString("SID", sid)
                            xw.WriteElementString("Title", rdr("Title").ToString())
                            xw.WriteElementString("Body", rdr("Body").ToString())
                            xw.WriteEndElement()
                            xw.WriteEndDocument()
                        End Using
                        If File.Exists(finalFile) Then File.Delete(finalFile)
                        File.Move(tmpFile, finalFile)
                    End While
                End Using
            End Using
        End Using
        Return True
    Catch ex As Exception
        ' log exception
        Return False
    End Try
End Function

Private Function SanitizeFileName(name As String) As String
    For Each c In Path.GetInvalidFileNameChars()
        name = name.Replace(c, "-"c)
    Next
    Return name
End Function

Practical notes: sanitize names, avoid placing huge numbers of files in one folder (partition by date or ID range), favor the single-connection streaming approach for 500–2000 records/day, and only parallelize file writes if the storage subsystem can handle concurrent IO. If running inside ASP.NET, perform the export off-request (background worker or external service) to avoid tying up request threads.

Recommended Answers

All 5 Replies

Member Avatar for 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
Member Avatar for SheSaidImaPregy

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

Member Avatar for 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

Member Avatar for 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!

Member Avatar for 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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.