excelxmlvbamsxml6

SelectSingleNode failure in VBA

I'm trying to parse a returned data service file in VBA for Excel, and while I've reviewed several helpful answers to similar questions, none of them seem to get SelectSingleNode to return anything for me.

The XML file is rather large, but the wrapper nodes are thus:

<?xml version="1.0" encoding="utf-8"?>
<feed xml:base="https://___.bpmonline.com/0/ServiceModel/EntityDataService.svc/" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <title type="text">labQuoteCollection</title>
    ...
</feed>

My relevant VBA code is here (I know some of those properties are redundant, just trying to be certain):

Dim NS As String
NS = "xmlns:a=""http://www.w3.org/2005/Atom"" xmlns:d=""http://schemas.microsoft.com/ado/2007/08/dataservices"" xmlns:m=""http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"""
Dim QuoteData As String
Dim oXMLReq As New WinHttp.WinHttpRequest
Dim oXMLDoc As New MSXML2.DOMDocument60
With oXMLHTTP
    .Open "GET", DataURL, False
    .setRequestHeader "Cookie", AuthCookie
    .send

    QuoteData = .responseXML.XML
    oXMLDoc.LoadXML QuoteData
    oXMLDoc.setProperty "SelectionLanguage", "XPath"
    oXMLDoc.setProperty "SelectionNamespaces", NS
    oXMLDoc.resolveExternals = True
    Debug.Print (oXMLDoc.parseError.ErrorCode)
    Debug.Print oXMLDoc.XML
    Dim Quotes As IXMLDOMNode
    Quotes = oXMLDoc.SelectSingleNode("//a:feed")
    If Quotes Is Nothing Then
        Debug.Print "fail"
    End If
End With

I can see that the XML is loaded into oXMLDoc without error, and the Print statement outputs it in its entirety, but none of the XPath queries I've attempted have returned anything. The other questions I've seen on the topic suggest that the above query is correct; am I perhaps missing something else?

Solution 
  • Quotes = oXMLDoc.SelectSingleNode("//a:feed")

    should be

    Set Quotes = oXMLDoc.SelectSingleNode("//a:feed")

    The error message is misleading since it looks like it's saying the xpath target was not found, but if that happens it doesn't raise an error, it just returns Nothing