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