Excel VBA - Nested Data in XML file

Question

Looking for a little bit of help here. I have an XML file that I need to get a value from an item in the list and drop it in a spreadsheet. The "getting it in the spreadsheet" code is not listed here as I have that part working from a previous project that works.

Here is an example of the XML:

<Level1 Name="Top">
    <Level2 Name="First" id="1-0">
        <Level3 Name="First First" id="1-1" />
        <Level3 Name="Second First" id="1-2" />
        <Level3 Name="Third First" id="1-3" />
    </Level2>         **EDITED***
    <Level2 Name="Second" id="1-0">
        <Level3 Name="First Second" id="2-1" />
        <Level3 Name="Second Second" id="2-2" />
        <Level3 Name="Third Second" id="2-3" />
    </Level2>
</Level1>

I am attempting to retrieve the id from each Level3 for each Level2. I can't quite get the Node List to cycle through. It keeps "skipping" the For.

Sub Stuff()
    Dim XMLFileName As String
    Dim oXMLFile As New MSXML2.DOMDocument60
    Dim XMLSNL1 As IXMLDOMNodeList               'XML Source...(NodeList/Node/Element)
    Dim XMLSE1 As IXMLDOMElement
    Dim XMLSN1 As IXMLDOMNode
    Dim XMLSNL2 As IXMLDOMNodeList
    Dim XMLSN2 As IXMLDOMElement
    Dim XMLSN2 As IXMLDOMNode
    Dim TempStr as String          'Using this to more easily watch (for me) the data

    oXMLFile.async = False
    oXMLFile.validateOnParse = False

    oXMLFile.Load (XMLFileName)    'I use the actual filename here. Verified it comes in

    Set XMLSNL1 = oXMLFile.SelectNodes("//Level1/Level2")
    Set XMLSNL2 = oXMLFile.SelectNodes("//Level1/Level2/Level3")
    
    For Each XMLSE1 In XMLSNL1
        TempStr = XMLSNL1.getAttribute(id)
    Next
End Sub

I do realize at this point I'm not going down to Level3 but I work progressively and trying to at least get data from Level2. Let me know if more info is needed and I'll make edits here. Thank you in advance for your time.

Answer 1

Firstly, your XML code is not correct... First Level2 tag is not closed. It must look like:

<Level1 Name="Top">
    <Level2 Name="First" id="1-0">
        <Level3 Name="First First" id="1-1" />
        <Level3 Name="Second First" id="1-2" />
        <Level3 Name="Third First" id="1-3" />
    </Level2>
    <Level2 Name="Second" id="1-0">
        <Level3 Name="First Second" id="2-1" />
        <Level3 Name="Second Second" id="2-2" />
        <Level3 Name="Third Second" id="2-3" />
    </Level2>
</Level1>

It is good to test if the XML is correct (in code). So, it would be good to use .validateOnParse = True and then test if the file is (Not) loaded. Please, test the next adapted code:

Sub Stuff()
    Dim XMLFileName As String
    Dim oXMLFile As New MSXML2.DOMDocument60
    Dim XMLSNL1 As IXMLDOMNodeList
    Dim XMLSE1 As IXMLDOMElement
    Dim XMLSE2 As IXMLDOMElement
    Dim XMLSNL2 As IXMLDOMNodeList
    
    XMLFileName = ThisWorkbook.Path & "\TesteXML\TestLevels.xml" 'use here your file path (if extracted from a local file)
    If Dir(XMLFileName) = "" Then MsgBox "Wrong file full name...": Exit Sub 'for local file
    
    With oXMLFile
        .async = False
        .validateOnParse = True 'if offer the possibility to check that XML is correct
        If Not .Load(XMLFileName) Then 'if not loaded (being incorrect) show the parse error 
                                       'description and number
           Debug.Print .parseError.reason, .parseError.ErrorCode: Exit Sub 'exist if not parsed
        End If

        Set XMLSNL1 = .SelectNodes("//Level1/Level2") 'set only a node list (since only Levell3 exists)
    End With

    Debug.Print XMLSNL1.Item(0).ParentNode.BaseName   'Level1
    For Each XMLSE1 In XMLSNL1
        Debug.Print , XMLSE1.BaseName & ": " & XMLSE1.getAttribute("Name") 'level2
        Set XMLSNL2 = XMLSE1.SelectNodes("Level3")
        For Each XMLSE2 In XMLSNL2
            Debug.Print , , XMLSE2.BaseName & ": " & XMLSE2.getAttribute("Name") 'level3
            Debug.Print , , , "Level3 ID = " & XMLSE2.getAttribute("id")
        Next XMLSE2
    Next
End Sub