Excel vba Parse Complex XML

Question

I need to get data from an xml file and load it to an Excel worksheet. I'm able to load the xml file and get element data in messagebox. This will written in VBA for Excel 2013

the xml file structure

<?xml version="1.0" encoding="utf-8" ?>
<Response >
    <Status>10</Status>
    <callMessage>Success.</callMessage>
    <PullR version="1">
        <responseStatus>1000</responseStatus>
        <responseMessage>Success.</responseMessage>
        <Options>
            <Option>
                <header>
                    <need1>text1</need1> ---- "COLUMN1"
                    <need2>
                        <![CDATA[text2]]>
                    </need2>  ---- "COLUMN2"
                    <need 3>this text3 together with need 2</need3>"COLUMN2"
                    <need4>
                        <![CDATA[Text4]]>
                    </need4> ---"COLUMN4"
                </header>
                <Details> - ""this one could be 1 to 10 child element (detail) this sample has 2 ""
                    <detail>
                        <need5>text5</need5>  " COLUMN5"
                        <need6>date6</need6>
                        <need7>time7</need7>
                        <need8>
                            <![CDATA[text8]]>
                        </need8>
                        </detail>
                    <detail> ---- ""this should be on second row in excel sheet ""
                        <need5>text5</need5> --- "COLUMN5 ROW +1"
                        <need6>date6</need6> ---- "COLUMN6 ROW +1"
                        <need7>time7</need7>
                        <need8>
                            <![CDATA[text8]]>
                        </need8>
                    </detail>
                </Details>
            </Option>
         </Options>
    </PullR>
</Response>

I reduce the xml file child elements there is many more , I'm stuck on the detail parent element part as when it writes the 2nd row it writes need1 for the next Option ancestor element. The date in the cell will be the text (atomic value) field which I inputed exactly as there are in the xml file.

---

Dim Options As IXMLDOMNodeList
Dim Option As IXMLDOMNode

Set Options = rosDoc.SelectNodes("/Response/PullR/Options/Option")

   For Each Option In Options

   MsgBox Trip.SelectNodes("header/need1").Item(0).Text
   MsgBox Trip.SelectNodes("Details/Detail/need5").Item(0).Text
   MsgBox Trip.SelectNodes("Details/Detail/need5").Item(1).Text
   Next

This code works perfect but need5 is variable most cases there is 2 items but sometimes there might be more how will I go by making a count off need5 within option

Answer 1

VBA supports xpath. This is imo the easiest way to process data from xml files.

Here is some sample code that will get you started:

Dim doc As DOMDocument
Set doc = New DOMDocument
doc.Load "C:\x.xml"
Dim Variables As IXMLDOMNodeList
Dim variable As IXMLDOMNode
Set Variables = doc.SelectNodes("/Environment/Variable")
    For Each variable In Variables
        Debug.Print variable.SelectNodes("Caption").Item(0).Text
        Debug.Print variable.SelectNodes("Type").Item(0).Text
    Next

Answer 2

Thanks again David G I figured it out

this was my final code maybe someone could help clean up a bit since i have to write the need5 a few times

Dim Options As IXMLDOMNodeList
Dim Option As IXMLDOMNode

Set Options = rosDoc.SelectNodes("/Response/PullR/Options/Option")

   For Each Option In Options

   MsgBox Trip.SelectNodes("header/need1").Item(0).Text
   MsgBox Trip.SelectNodes("Details/Detail/need5").Item(0).Text

   IF not Trip.SelectNodes("Details/Detail/need5").Item(1) is nothing then 
   MsgBox Trip.SelectNodes("Details/Detail/need5").Item(1).Text

   IF not Trip.SelectNodes("Details/Detail/need5").Item(2) is nothing then 
   MsgBox Trip.SelectNodes("Details/Detail/need5").Item(2).Text

   'etc........

   Next