excel - 如何获取不是来自第一个子 XML VBA API 的记录

Question

我从世界银行得到了这样的数据(来源:http://api.worldbank.org/V2/country?incomeLevel=LIC)要使用此 VBA 代码，您需要设置引用微软 winhttp 服务微软XML微软2.0对象库

<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>

我的代码:

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

它一直有效到这里:

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

它仅适用于第一个 child - 获取国家/地区代码，但我需要例如获取 wb:name (全名)我将非常感谢任何提示

Answer 1

这里有一些可以实现你想要的功能:

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