CODEWITHSUNDEEP
vbaxml-parsing
WolfSkin
WolfSkin

Reputation: 93

VBA to read XML nodes with similar name

I have a XML which looks like 

    <view>
<fieldlist>
<viewField>
<formatterParameterMap>
<entry>

<string>    </string>
<string     </string>

</entry>
</formatterParameterMap>
</viewField>
</fieldlist>
<view>

My current code can only read the first string information ( text ) However i need to read all the node.

My current code 

'list data source information
Private Sub CommandButton1_Click()
Dim Init As Integer
Dim xmlDoc As New DOMDocument
Dim DomNode  As IXMLDOMNode
Init = 7


Sheets("Views").Select
XML_Path = Cells(3, "F").Value
'Set xmlDoc = CreateObject("MSXML2.DOMDocument")

'MsgBox XML_Path

xmlDoc.Load ("C:\DRIVE\BMC\DSS\01_DSS_NExT\SP_1\IN APP\Automation_YF\Total_YF_XML.xml")
child_count = 0

For Each DomNode In xmlDoc.getElementsByTagName("entry")                      'Itrate through the SubViewList
ActiveSheet.Cells(Init, 1) = DomNode.SelectNodes("string")(0).Text
'ActiveSheet.Cells(Init, 2) = DomNode.SelectNodes("entry")(0).Text

child_count = child_count + 1

Init = Init + 1
Next DomNode
Dim node As IXMLDOMNode
Dim childNode As IXMLDOMNode

End Sub

I am struggling to get this working. Any help would be honestly appreciated

Upvotes: 1

Views: 2219

Answers (1)

Cool Blue
Cool Blue

Reputation: 6476

In order to use getElementsByTagName at the second level, need to declare DomNode As IXMLDOMElement, not IXMLDOMNode.

Also, your xml is dodgy - your second view tag should be closing and one of your string tags is missing a ">" - see properly formed sample XML below the code...

enter image description here

Private Sub CommandButton1_Click()
Dim Init As Integer
Dim xmlDoc As New DOMDocument
Dim DomNode  As IXMLDOMElement
Dim childNode As IXMLDOMNode
Init = 7


Sheets("Views").Select
'XML_Path = Cells(3, "F").Value
'Set xmlDoc = CreateObject("MSXML2.DOMDocument")

'MsgBox XML_Path

xmlDoc.Load ("C:\DRIVE\BMC\DSS\01_DSS_NExT\SP_1\IN APP\Automation_YF\Total_YF_XML.xml")

'Assemble the qualified node list and walk it
For Each DomNode In xmlDoc.getElementsByTagName("entry")

' Assemble the qualified node list, walk it and output their text
  For Each childNode In DomNode.getElementsByTagName("string")
    ActiveSheet.Cells(Init, 1).Value2 = childNode.Text
    Init = Init + 1
  Next childNode

Next DomNode

End Sub

EDIT: Alternate solution using SelectNodes on IXMLDOMNode as pointed out by OP.

Private Sub CommandButton1_Click()
Dim Init As Integer
Dim xmlDoc As New DOMDocument
Dim DomNode  As IXMLDOMNode
Dim childNode As IXMLDOMNode
Init = 7


Sheets("Views").Select
'XML_Path = Cells(3, "F").Value
'Set xmlDoc = CreateObject("MSXML2.DOMDocument")

'MsgBox XML_Path

xmlDoc.Load ("C:\DRIVE\BMC\DSS\01_DSS_NExT\SP_1\IN APP\Automation_YF\Total_YF_XML.xml")

'Assemble the qualified node list and walk it
For Each DomNode In xmlDoc.getElementsByTagName("entry")

' Assemble the qualified node list, walk it and output their text
  For Each childNode In DomNode.SelectNodes("string")
    ActiveSheet.Cells(Init, 1).Value2 = childNode.Text
    Init = Init + 1
  Next childNode

Next DomNode

End Sub

Proper XML sample...

<?xml version="1.0" encoding="utf-8"?>
<view>
    <fieldlist>
        <viewField>
            <formatterParameterMap>
                <entry>    
                    <string>8888</string>
                    <string>99999</string>
                </entry>
            </formatterParameterMap>
        </viewField>
    </fieldlist>
    <fieldlist>
        <viewField>
            <formatterParameterMap>
                <entry>
                    <string>22222222</string>
                    <string>2323232323</string>
                </entry>
            </formatterParameterMap>
        </viewField>
    </fieldlist>
    <fieldlist>
        <viewField>
            <formatterParameterMap>
                <entry>
                    <string>36363636</string>
                    <string>3737373737</string>
                </entry>
            </formatterParameterMap>
        </viewField>
    </fieldlist>
    <fieldlist>
        <viewField>
            <formatterParameterMap>
                <entry>
                    <string>50505050</string>
                    <string>5151515151</string>
                </entry>
            </formatterParameterMap>
        </viewField>
    </fieldlist>
    <fieldlist>
        <viewField>
            <formatterParameterMap>
                <entry>
                    <string>64646464</string>
                    <string>6565656565</string>
                </entry>
            </formatterParameterMap>
        </viewField>
    </fieldlist>
    <fieldlist>
        <viewField>
            <formatterParameterMap>
                <entry>
                    <string>78787878</string>
                    <string>7979797979</string>
                </entry>
            </formatterParameterMap>
        </viewField>
    </fieldlist>
</view>

Upvotes: 2

Related Questions