Searching through highest node level in XML Document

Question

I'm working on a VBA-macro for Excel that automatically imports values from XML-Files into an array and the pastes them into a certain Worksheet. I simplified the code and pasted it below. Currently it searches for the node "TimeSeriesIdentification" in the nodelist "AllocationTimeSeries" and copies the values into the array (I would create a multiple dimension array to store the other entries as well). Although it occurs only once in the document, I wanted to save the node "DocumentVersion" as well. But what should I declare as the xmlNodeList, since "DocumentVersion" is on the same tree level as "AllocationTimeSeries"? "TotalAllocationResultDocument" doesn't work unfortunately...

Dim xmlDoc As MSXML2.DOMDocument60
Dim xmlElement As MSXML2.IXMLDOMElement
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
Dim xmlNode As MSXML2.IXMLDOMNode
Dim xmlAttribute As MSXML2.IXMLDOMAttribute
Dim strFilePath As String
Dim arrx As Integer
Dim arrStrings As Variant

Set xmlDoc = New MSXML2.DOMDocument60
    With xmlDoc
        .async = False
        .setProperty "ProhibitDTD", False
        .validateOnParse = False
        .resolveExternals = False
    End With

strFilePath = "C:\Desktop\testfolder\testfile.xml"

If Not xmlDoc.Load(strFilePath) Then
    MsgBox ("File loading failed!")
    Exit Sub
End If

Set xmlElement = xmlDoc.DocumentElement
Set xmlNodeList = xmlElement.SelectNodes("AllocationTimeSeries")
arrx = 1
ReDim arrStrings(100) As Variant

For Each xmlNode In xmlNodeList
    arrStrings(arrx) = xmlNode.SelectSingleNode("TimeSeriesIdentification").Attributes.getNamedItem("v").Text
Next xmlNode

Worksheets("Table1").Activate

For i = 1 To arrx
    Cells (1 + i, 1).Value = arrStrings(i)
Next i

Set xmlDoc = Nothing

And this would be a part of the xml I'm working with:

-<TotalAllocationResultDocument xsi:noNamespaceSchemaLocation="total-allocation-result-document.xsd" DtdVersion="4" DtdRelease="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <DocumentIdentification v="DAILYPRODU-170301-01"/>
    <DocumentVersion v="1"/>
    <DocumentType v="A25"/>
    <SenderIdentification v="SENDERA" codingScheme="A01"/>
    <ReceiverIdentification v="RECEIVERA" codingScheme="A01"/>
   -<AllocationTimeSeries>
        <TimeSeriesIdentification v="TotalAllocationResults_TS_1982400"/>
        <BidDocumentVersion v="2"/>
        <AuctionIdentification v="D-DAILYPRODU-170301-01"/>
       -<Period>
            <TimeInterval v="2017-02-28T23:00Z/2017-03-01T23:00Z"/>
            <Resolution v="PT60M"/>
           -<Interval>
                <Pos v="1"/>
                <Qty v="1.0"/>
                <PriceAmount v="14.42"/>
            </Interval>
           -<Interval>
                <Pos v="2"/>
                <Qty v="3.0"/>
                <PriceAmount v="14.65"/>
           -<Interval>
        </Period>
    </AllocationTimeSeries>
   -<AllocationTimeSeries>
        <TimeSeriesIdentification v="TotalAllocationResults_TS_1982400"/>
        <BidDocumentVersion v="2"/>
        <AuctionIdentification v="D-DAILYPRODU-170301-01"/>
       -<Period>
            <TimeInterval v="2017-02-28T23:00Z/2017-03-01T23:00Z"/>
            <Resolution v="PT60M"/>
           -<Interval>
                <Pos v="1"/>
                <Qty v="5.0"/>
                <PriceAmount v="14.02"/>
            </Interval>
           -<Interval>
                <Pos v="2"/>
                <Qty v="3.0"/>
                <PriceAmount v="14.67"/>
           -<Interval>
        </Period>
    </AllocationTimeSeries>
</TotalAllocationResultDocument>

I apologize for my bad use of terms and chaotic coding structure, I just started with VBA like two weeks ago and still have a lot to learn.

Answer 1

Simply use the xmlElement to select its child DocumentVersion as it will not fall under AllocationTimeSeries node list. Also, below code runs a few adjustments you can consider:

1. No need to store parsed xml data in any array but directly output values to worksheet.
2. In general VBA programming, use error handling and specifically in this case use MSXML's parseError to replace the If on Load call.
3. In general VBA programming, use Option Explicit at the top of the module (above all macros) which raises compile errors on unassigned objects/variables which current code maintains.
4. Uninitialize all set objects, not just xmlDoc, to release such resources even if program encounters an error. See implementation in error handling.
5. Be sure you work with a well-formed XML file. Your posted sample does not have <Interval> nodes properly closed which will raise a runtime error.

VBA

Option Explicit

Sub XMLParse()
On Error GoTo ErrHandle
    Dim strFilePath As String

    Dim xmlDoc As MSXML2.DOMDocument60
    Dim xmlElement As IXMLDOMElement
    Dim xmlNodeList As IXMLDOMNodeList
    Dim xmlNode As IXMLDOMNode

    Dim i As Long

    Set xmlDoc = New MSXML2.DOMDocument60
    With xmlDoc
        .async = False
        .setProperty "ProhibitDTD", False
        .validateOnParse = False
        .resolveExternals = False
    End With

    strFilePath = "C:\Desktop\testfolder\testfile.xml"
    xmlDoc.Load strFilePath

    Set xmlElement = xmlDoc.DocumentElement
    Set xmlNodeList = xmlElement.SelectNodes("AllocationTimeSeries")

    i = 1
    For Each xmlNode In xmlNodeList
        Worksheets("Table1").Cells(i, 1).Value = xmlElement.SelectSingleNode("DocumentVersion").Attributes.getNamedItem("v").Text
        Worksheets("Table1").Cells(i, 2).Value = xmlNode.SelectSingleNode("TimeSeriesIdentification").Attributes.getNamedItem("v").Text
        i = i + 1
    Next xmlNode

ExitSub:    
    Set xmlElement = Nothing
    Set xmlNodeList = Nothing
    Set xmlDoc = Nothing
    Exit Sub

ErrHandle:
    If xmlDoc.parseError.reason <> "" Then
        MsgBox xmlDoc.parseError.reason, vbCritical, "XML ERROR"
    Else
        MsgBox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
    End If
    Resume ExitSub
End Sub