Member Avatar for midnite11

I receive large pre-formatted XML files. Each XML is formatted the same way - in that they all have the same Parent Nodes and Child Nodes. Each Parent node has at least one Attribute (sometimes 3 or 4) and some fields also have attributes. In my opinion, the formatting of these xmls files is poor and very hard to work with.

The Parent Nodes are Databases Tables, the Child Nodes are Fields within the tables and the Attributes are Index Fields in the Tables.

My job - is to import these xml files into one database (lets say MSAccess in this example). This database already has the Tables, fields etc...the goal is to import one or more xml files into these tables to create a Consolidation of data.

I am successfully doing this now using XPath to navigate through the xml files basically record by record (field by field) and creating INSERT strings which then are used to import into the database tables via OLEDBCommands. Using...

-XPath XML Document
-XPath Navigator
-XPath Node Iterator

As mentioned, I am successfully doing this now. However, the reason I'm writing this post is that I am not at all happy with the performance. I've googled many times and every thing I see are examples of importing xml files that are alot more clean to work with (e.g. no attributes). The Attributes in these xml files are the number one PAIN as I need to retrieve them and include them in the sql INSERTS as indexs on rows.

I'm looking to the VB.net experts here for Ideas on how I can import this data faster.
An example of a portion of the xmls is below. Thank you in advance.

<Elements> - section of XML file
<Element> - Database Table
Nodes under <Element> are fields in the Element Table in the Database.

<Elements>
<Element id="ce1" cc_id="cc1" st_id="st1">
<WbsNum>TOTAL CONTRACT</WbsNum>
<ElemDesc>X-GEN Phase II</ElemDesc>
<ElemLevel>1</ElemLevel>
<ElemHier>1</ElemHier>
<ElemSeq>0</ElemSeq>
<NumChild>8</NumChild>
<SortVal>1P000215</SortVal>
<LinkVal>1P000215</LinkVal>
<LongDesc>X-GEN Phase II</LongDesc>
<NAFlag>0</NAFlag>
<RiskLevel>L</RiskLevel>
<CtrID ctr_id="ctr1"/>
<UserDefValue col_id="col70">ENGR</UserDefValue>
</Element>
<Element id="ce2" cc_id="cc1" st_id="st1">
<WbsNum>1</WbsNum>
<ElemDesc>X-GEN SHIP BADE</ElemDesc>
<ElemLevel>2</ElemLevel>
<ElemHier>2</ElemHier>
<ParentID ce_id="ce1"/>
<ElemSeq>0</ElemSeq>
<NumChild>5</NumChild>
<SortVal>1</SortVal>
<LinkVal>1</LinkVal>
<LongDesc>X-GEN SHIP BADESx X-GEN</LongDesc>
<NAFlag>0</NAFlag>
<RiskLevel>L</RiskLevel>
<CtrID ctr_id="ctr1"/>
<UserDefValue col_id="col70">ENGR</UserDefValue>
</Element>
</Elements>

  • 2 Contributors
  • 3 Replies
  • 270 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by ChrisPadgham

Dani AI

Generated

— you already have a working importer; the next gains come from changing how you parse and how you write to the DB. 's "read as text" idea is correct in spirit (avoid full in-memory DOM), but a safer, faster pattern is: stream the XML, transform or flatten attributes on the fly, and push records using a single prepared command inside batched transactions. That removes per-row SQL parsing and avoids repeated allocations from building SQL strings.

Practical, high-impact steps (in order of return on effort):

  • Stream-parse the file (XmlReader or equivalent) so memory never contains the whole document.
  • Use one parameterized OleDbCommand (reuse parameters) instead of concatenating INSERT strings.
  • Wrap inserts in a transaction and commit in batches (e.g., every 500–2000 rows).
  • If the target table has indexes, drop or disable them before import and rebuild afterwards — indexes kill insert throughput.
  • If Access performance is still limiting, import into a server DB that supports bulk copy, then move the consolidated data back to Access if needed.

Example pattern (VB.NET) — streaming + reusable parameterized command + batch commits:

' streaming parse + parameterized inserts + batch transactions
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\MyDb.accdb;"
Using cn As New OleDb.OleDbConnection(connString)
    cn.Open()
    Dim tx As OleDb.OleDbTransaction = cn.BeginTransaction()
    Dim cmd As New OleDb.OleDbCommand("INSERT INTO Element (id, cc_id, st_id, WbsNum, ElemDesc) VALUES (?,?,?,?,?)", cn, tx)
    cmd.Parameters.Add(New OleDb.OleDbParameter("id", OleDb.OleDbType.VarChar))
    cmd.Parameters.Add(New OleDb.OleDbParameter("cc_id", OleDb.OleDbType.VarChar))
    cmd.Parameters.Add(New OleDb.OleDbParameter("st_id", OleDb.OleDbType.VarChar))
    cmd.Parameters.Add(New OleDb.OleDbParameter("WbsNum", OleDb.OleDbType.VarChar))
    cmd.Parameters.Add(New OleDb.OleDbParameter("ElemDesc", OleDb.OleDbType.VarChar))

    Dim settings As New Xml.XmlReaderSettings() With {.IgnoreWhitespace = True}
    Using xr As Xml.XmlReader = Xml.XmlReader.Create("C:\path\large.xml", settings)
        Dim rows As Integer = 0
        While xr.Read()
            If xr.NodeType = Xml.XmlNodeType.Element AndAlso xr.Name = "Element" Then
                cmd.Parameters(0).Value = If(xr.GetAttribute("id"), DBNull.Value)
                cmd.Parameters(1).Value = If(xr.GetAttribute("cc_id"), DBNull.Value)
                cmd.Parameters(2).Value = If(xr.GetAttribute("st_id"), DBNull.Value)
                ' set remaining params from child elements...
                cmd.ExecuteNonQuery()
                rows += 1
                If rows Mod 1000 = 0 Then
                    tx.Commit()
                    tx = cn.BeginTransaction()
                    cmd.Transaction = tx
                End If
            End If
        End While
    End Using
    tx.Commit()
End Using

Sanitization note and troubleshooting: if files contain invalid XML characters, run a lightweight streaming sanitizer before parsing (strip illegal control chars rather than loading into memory). Time each stage (parse vs DB) to find the real bottleneck. When testing, try different batch sizes, and always back up the Access file before dropping indexes or running large imports (Access can bloat — compact/repair afterward). These changes typically move an importer from “too slow” to “practical” without rewriting the whole pipeline.

Recommended Answers

All 3 Replies

Member Avatar for ChrisPadgham

you can use DOMdocument to process XML in VB,

you use the method LoadXML(pathname) to load and there methods and properties to iterate through the XML file. A quick google search will show you how.

Member Avatar for midnite11

you can use DOMdocument to process XML in VB,

you use the method LoadXML(pathname) to load and there methods and properties to iterate through the XML file. A quick google search will show you how.

What's different than what I'm doing now? Still have to 1) load xml and 2) iterated through the xml. I use XPath Navigator to do this. Either XPath or DOMDocument still requires node node iteration.

These files are very large and loading the entire xml file into memory has created problems. LoadXML loads the entire xml file - this would at times fail with some of the files I need to read. I also have to ignore bad xml characters - as the creation of these files are not always xml compliant. I've researched, tried, researched tried and have come to use the following as my conclusion. It works - always - but I'm still looking for a faster way to 1) read/iterate 2) save to database - predefined tables/fields

Dim sr As New System.IO.StreamReader(myfile, System.Text.Encoding.GetEncoding(1250))
Dim myreader As New XmlTextReader(sr)
myreader.XmlResolver = Nothing
xpathDoc = New XPathDocument(myreader)
xmlNav = xpathDoc.CreateNavigator
xmINI = xmlNav.Select("NodeSection/NodeTable/*")
...iterate child nodes, attributes etc...create sql insert
...navigate to next Node Section (different DB Table)...repeat
Member Avatar for ChrisPadgham

Then just open the file as at text file and read it line by line processing as you go. There is a lot more coding involved by it is much much faster to execute.

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.