VBA Get XMLnode "time" of ECB exchange rates via GET-Request

Question

i'm requesting the exchange rates from ECB via GET from https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml. Those received exchange rates should be persisted in a database table, but only, if those are not already requested and stored beforehand. Structure of the XML-File is the following:

<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time="2023-01-06">
<Cube currency="USD" rate="1.0500"/>
<Cube currency="JPY" rate="141.30"/>
<Cube currency="BGN" rate="1.9558"/>
<Cube currency="CZK" rate="24.054"/>
<Cube currency="DKK" rate="7.4370"/>
<Cube currency="GBP" rate="0.88475"/>
<Cube currency="HUF" rate="396.96"/>
<Cube currency="PLN" rate="4.6950"/>
<Cube currency="RON" rate="4.9296"/>
<Cube currency="SEK" rate="11.2580"/>
<Cube currency="CHF" rate="0.9864"/>
<Cube currency="ISK" rate="152.90"/>
<Cube currency="NOK" rate="10.8070"/>
<Cube currency="TRY" rate="19.7065"/>
<Cube currency="AUD" rate="1.5590"/>
<Cube currency="BRL" rate="5.6034"/>
<Cube currency="CAD" rate="1.4331"/>
<Cube currency="CNY" rate="7.2045"/>
<Cube currency="HKD" rate="8.2006"/>
<Cube currency="IDR" rate="16468.30"/>
<Cube currency="ILS" rate="3.7361"/>
<Cube currency="INR" rate="86.9103"/>
<Cube currency="KRW" rate="1337.18"/>
<Cube currency="MXN" rate="20.2645"/>
<Cube currency="MYR" rate="4.6237"/>
<Cube currency="NZD" rate="1.6889"/>
<Cube currency="PHP" rate="58.374"/>
<Cube currency="SGD" rate="1.4156"/>
<Cube currency="THB" rate="35.737"/>
<Cube currency="ZAR" rate="18.2092"/>
</Cube>
</Cube>
</gesmes:Envelope>

What i need is the value of "time" in cube: to check, if i need to store or discard them.

The VBA-code to query and process is the following:

Dim xhr As Object, node As Object
    ' Set of collection for exchance rates
    Set exchangeRates = New Collection

    ' ServerXMLHTTP object for GET requests of exchange rates of ECB
    Set xhr = CreateObject("msxml2.xmlhttp.6.0")
    xhr.Open "GET", "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml", False
    xhr.Send
    ' Processing retrieved exchange rates and storing into created collection for further use
    For Each node In xhr.responseXML.SelectNodes("//*[@rate]")

      exchangeRates.Add Conversion.Val(node.GetAttribute("rate")), node.GetAttribute("currency")
      lb_CurrencySelection.AddItem node.GetAttribute("currency")
      
    Next`

How can i access the node "time" from parent Cube? I tried via SelectNodes, but failed.

Thanks in advance and greetings

Answer 1

If you're accessing the nodes already you can try the following:

Set nodes = xhr.responseXML.SelectNodes("//*[@rate]")
time = nodes(0).ParentNode.Attributes.getNamedItem("time").Value

For Each node in nodes
    exchangeRates.Add Conversion.Val(node.GetAttribute("rate")), node.GetAttribute("currency")
    lb_CurrencySelection.AddItem node.GetAttribute("currency")
Next node