Quickest Way to Parse XML

Question

I have huge XML files, approx 1GB each. They are so big that it can't be opened even in Notepad++ due to large volume of data.

I edited the XML and was able to parse it through DOMDocument60 (Thanks to the help provided at stackflow).

I was reading similar question at Improve speed of VBA but I am still not able to implement it properly, so need some guidance.

For example:

1. How to load the xml in SAX ? Do i need to load it in DOMDocument60 before reading in SAX ?
2. How to read line by line in SAX once imported ? In DOMDocument60, i can easily jump to any node and then loop through the childnodes but not sure how can i do this in SAX ?
3. What time difference SAX can make to parsing huge XML, compared to DOMDocument? I haven't found any real time example on this.
4. Is there any better option available than SAX in VBA, like any other library which I can use to speed up the process of Parsing.

Appreciate your suggestions. (Sample XML file is below)

<ParentNode type="actual">

<SampleObject class="POC" version="XYZ123" distName="Test1" id="Sample">
  <p name="name">POC1</p>
  <p name="object1">0</p>
  <p name="object2">6</p>
  <p name="object3">0</p>
</SampleObject>

<SampleObject class="POC" version="XYZ123" distName="Test2" id="Sample">
  <p name="name">POC1</p>
  <p name="object1">2</p>
  <p name="object2">10</p>
  <p name="object4">4</p>
  <p name="object3">6</p>
</SampleObject>

<SampleObject class="POC" version="XYZ123" distName="Test3" id="Sample">
  <p name="name">POC1</p>
  <p name="object2">90</p>
  <p name="object3">0</p>
</SampleObject>

<SampleObject class="POC" version="XYZ123" distName="Test4" id="Sample">
  <p name="name">POC1</p>
  <p name="object1">2</p>
  <p name="object2">10</p>
  <p name="object4">40</p>
  <p name="object3">61</p>
</SampleObject>

Answer 1

Here's where I got following the link I posted above and using your sample XML. Just outputs to the Immediate window: I don't know what you're doing with the extracted data...

Test Method in regular module:

Sub Tester()

    Const FNAME As String = "example.xml"
    Dim rdr As New MSXML2.SAXXMLReader30
    Dim cnth As New ContentHandler

    Set rdr.ContentHandler = cnth
    rdr.parseURL ThisWorkbook.Path & "\" & FNAME  'test xml file is in same folder as the workbook

End Sub

Class module ContentHandler:

Option Explicit

Implements IVBSAXContentHandler

Dim cls, vers, distName, id, pName, pContent
Dim inSO As Boolean, inP As Boolean

Private Sub IVBSAXContentHandler_characters(strChars As String)
    If inP Then Debug.Print "P content:", strChars
End Sub

Private Sub IVBSAXContentHandler_startElement(strNamespaceURI As String, _
                             strLocalName As String, strQName As String, _
                             ByVal oAttributes As MSXML2.IVBSAXAttributes)
    Select Case strLocalName
        Case "SampleObject"
            inSO = True
            cls = oAttributes.getValueFromName("", "class")
            vers = oAttributes.getValueFromName("", "version")
            distName = oAttributes.getValueFromName("", "distName")
            id = oAttributes.getValueFromName("", "id")
            Debug.Print "Start", strLocalName, cls, vers, distName, id
        Case "p"
            inP = True
            pName = oAttributes.getValueFromName("", "name")
            Debug.Print "Start", strLocalName, pName
    End Select
End Sub

Private Sub IVBSAXContentHandler_endElement(strNamespaceURI As String, strLocalName As String, strQName As String)
    Select Case strLocalName
        Case "SampleObject"
            inSO = False
            cls = ""
            vers = ""
            distName = ""
            id = ""
        Case "p"
            pName = ""
            inP = False
    End Select
End Sub

Private Property Set IVBSAXContentHandler_documentLocator( _
                            ByVal RHS As MSXML2.IVBSAXLocator)
End Property

Private Sub IVBSAXContentHandler_startDocument()
End Sub

Private Sub IVBSAXContentHandler_endDocument()
End Sub

Private Sub IVBSAXContentHandler_endPrefixMapping(strPrefix As String)
End Sub

Private Sub IVBSAXContentHandler_ignorableWhitespace(strChars As String)
End Sub

Private Sub IVBSAXContentHandler_processingInstruction(strTarget As String, strData As String)
End Sub

Private Sub IVBSAXContentHandler_skippedEntity(strName As String)
End Sub

Private Sub IVBSAXContentHandler_startPrefixMapping(strPrefix As String, strURI As String)
End Sub