excelxmlvbachild-nodes

How to get a record not from first child XML VBA API


I've got data from WorldBank like this (source: http://api.worldbank.org/V2/country?incomeLevel=LIC) To use this VBA code you need to set References microsoft winhttp services microsoft xml microsoft 2.0 object library

<wb:countries xmlns:wb="http://www.worldbank.org" page="1" pages="1" per_page="50" total="34">
<wb:country id="AFG">
<wb:iso2Code>AF</wb:iso2Code>
<wb:name>Afghanistan</wb:name>
<wb:region id="SAS" iso2code="8S">South Asia</wb:region>
<wb:adminregion id="SAS" iso2code="8S">South Asia</wb:adminregion>
<wb:incomeLevel id="LIC" iso2code="XM">Low income</wb:incomeLevel>
<wb:lendingType id="IDX" iso2code="XI">IDA</wb:lendingType>
<wb:capitalCity>Kabul</wb:capitalCity>
<wb:longitude>69.1761</wb:longitude>
<wb:latitude>34.5228</wb:latitude>
</wb:country>

My code:

Dim strURL As String
Dim ws As Worksheet

Set ws = Worksheets("API")

strURL = ws.[API_URL]

Dim hReq As New WinHttpRequest
hReq.Open "GET", strURL, False 
hReq.Send 

Dim strResp As String
strResp = hReq.ResponseText
Dim xmlDoc As New MSXML2.DOMDocument
If Not xmlDoc.LoadXML(Right(strResp, Len(strResp) - 1)) Then
    MsgBox ("Błąd ładowania URL")
End If

Dim xnodelist As MSXML2.IXMLDOMNodeList
Set xnodelist = xmlDoc.getElementsByTagName("wb:countries")

Dim xNode As MSXML2.IXMLDOMNode
Set xNode = xnodelist.Item(0)

Dim obAtt1 As MSXML2.IXMLDOMAttribute
Dim obAtt2 As MSXML2.IXMLDOMAttribute

Dim xChild As MSXML2.IXMLDOMNode
Dim xChild2 As MSXML2.IXMLDOMNode

Dim intRow As Integer
intRow = 3

Dim dtVal As String
Dim dblRate As String
Dim strVal As String

it works until here:

For Each xChild In xNode.ChildNodes

    Set obAtt1 = xChild.Attributes.getNamedItem("id")

    strVal = Trim(obAtt1.Text)

    ws.Cells(intRow, 2) = obAtt1.Text
    intRow = intRow + 1

Next xChild

it works only for first child - gets country code but I need for example to get wb:name (full name) I would be very thankful for any hints


Solution

  • Here's something which does what you want:

    Dim xmlDoc As New MSXML2.DOMDocument
    Dim countries As MSXML2.IXMLDOMNodeList, country As MSXML2.IXMLDOMNode
    
    'need these next two....
    xmlDoc.setProperty "SelectionLanguage", "XPath"
    xmlDoc.setProperty "SelectionNamespaces", "xmlns:wb='http://www.worldbank.org'"
    
    'loading from a local file for testing
    If Not xmlDoc.Load(ThisWorkbook.Path & "\country.xml") Then
        MsgBox ("Blad ladowania URL")
        Exit Sub
    End If
    
    Set countries = xmlDoc.SelectNodes("//wb:country")
    
    Debug.Print countries.Length
    
    For Each country In countries
        Debug.Print "------------------------------------"
        Debug.Print "id", country.Attributes.getNamedItem("id").Text
        Debug.Print "Name", country.SelectSingleNode("wb:name").nodeTypedValue
        Debug.Print "Region", country.SelectSingleNode("wb:region").nodeTypedValue
        'etc
    Next country