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

Question

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

score 3 · Accepted Answer · answered Mar 13 '19 at 22:05

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

Hint:-) Using `Dim xmlDoc As New MSXML2.DOMDocument60` referring to the current (recommended) **version 6.0**, you even needn't set the XPath property explicitly as XPath 1.0 is already fully supported. `MSXML2.DOMDocument` automatically refers to the last stable *version 3.0* sometimes necessary for compatibility reasons; it allows the integration of XPath by explicit selection language setting as demonstrated above. — T.M., Mar 14 '19 at 09:16

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

1 Answers1