1

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
Any1There
  • 186
  • 1
  • 8
  • You can prefix the elements with the correct namespace. It's a bit of a nuisance but hey.... – MacroMarc Mar 06 '21 at 17:27
  • 1
    as per MacroMarc's comment add `.SetProperty "SelectionNamespaces", "xmlns:r='http://www.ecb.int/vocabulary/2002-08-01/eurofxref'"` for you Dom60 then later reference with `Set xNodeList60 = xDoc60.SelectNodes("//r:Cube[@time]")` – QHarr Mar 06 '21 at 17:36
  • heheh - follow the snippet and the older question for example if you need elements from both namespaces: Dim ns1 As String, ns2 As String, nameSpaces As String ns1 = "xmlns:doc='http://www.ecb.int/vocabulary/2002-08-01/eurofxref'" ns2 = "xmlns:gesmes='http://www.gesmes.org/xml/2002-08-01'" xDoc60.SetProperty "SelectionNamespaces", ns1 & " " & ns2 – MacroMarc Mar 06 '21 at 17:45

0 Answers0