Parse large xml file into excel using vba

[tochins]

Hello,

I have a code with which I can parse a file of around 1MB. However I am looking to parse an xml file of about 128MB.

Below is my code

Dim xml_doc As New DOMDocument
Dim brtn As Boolean
 
Dim xnode As IXMLDOMElement
Dim snode As IXMLDOMElement
 
Dim intr As Long
Dim tmp As Long
 
 
 
Sheet1.Cells.Clear
 
'
file = Application.GetOpenFilename("Choose xml File (*.xml*),*.*", , "Openxml File")
brtn = xml_doc.Load(file)
 
intr = 2
tmp = 2
 
Sheet1.Cells.ClearFormats
 
If brtn Then
 
    With Sheets("Sheet1")
    .Cells(1, 1) = "Node"
    .Cells(1, 2) = "Version"
    .Cells(1, 3) = "distName"
    .Cells(1, 4) = "ID"
    .Cells(1, 5) = "Managed Object"
    .Cells(1, 6) = "Value"
 
 
 
    Range("A1", "F1").Font.Bold = True
    Sheet1.Range("A1", "F1").HorizontalAlignment = xlCenter
 
 
 
 
          For Each snode In xml_doc.SelectNodes("//managedObject")
 
            For Each xnode In snode.ChildNodes
 
 
 
 
                  .Cells(intr, 1) = snode.getAttribute("class")
                  .Cells(intr, 2) = snode.getAttribute("version")
                  .Cells(intr, 3) = snode.getAttribute("distName")
                  .Cells(intr, 4) = snode.getAttribute("id")
                  .Cells(intr, 5) = xnode.getAttribute("name")
                  .Cells(intr, 6) = xnode.Text
 
 
                  intr = intr + 1
 
            Next
 
          Next
 
 
 
 
 
      End With
Else
MsgBox "Unable to open xml file...."
 
End If
EndParse:
End Sub

Please let me know if there is any possible way to load around 250000 elements.

I read in some other article that DOM loads the whole file before parsing. The file size is more than 128 MB so my PC gets hang before showing result in the excel file.

It also read SAX is the efficient method. So I am not sure which one to use SAX or an array or stream all elements one by one?

Thank you

Chins