I'm trying to parse a set of FX quotes in a daily download from an ECB URL. It seems to work using DOMDocument but doesn't work when I switch to DOMDocument60. I've included both approaches in the code below. I believe I've got the XPath syntax correct -- to identify all Cube nodes with a "time" element so as to be able to identify each new day's quotations in the time series.
Can anyone tell me what I'm doing wrong?
Sub XMLDom60vs30()
Dim oXMLHTTP As Object
Dim sURL As String
Dim XmlMapResponse As String
Dim strXML As String
Dim xDoc As Object
Dim xDoc60 As MSXML2.DOMDocument60
Dim xNodeList As Object
Dim xNodeList60 As MSXML2.IXMLDOMNodeList
' This URL access daily FX history from an ECB website.
sURL = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml?affd3fe4c0ac916ce2e9d1ccfea2327c"
Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Set xDoc = CreateObject("MSXML2.DOMDocument")
Set xDoc60 = New MSXML2.DOMDocument60
oXMLHTTP.Open "GET", sURL, False
oXMLHTTP.send
XmlMapResponse = oXMLHTTP.responseText
strXML = XmlMapResponse
With xDoc
.async = False
.validateOnParse = False
.LoadXML (strXML)
End With
With xDoc60
.async = False
.validateOnParse = False
.LoadXML (strXML)
End With
Set xNodeList = xDoc.SelectNodes("//Cube[@time]")
Set xNodeList60 = xDoc60.SelectNodes("//Cube[@time]")
Debug.Print "List Length: xNodes = " & xNodeList.Length & ", XNodes60 = " & xNodeList60.Length & ", finished at " & Format(Now(), "hh:mm:ss")
End Sub