Search code examples
excelxmlvbaapidomdocument

Nodes in XML does not appear to be identified/utilizable

I am probably making some kind of stupid mistake here, but I'm stuck, so hopefully some of you can help me out.

I Have loaded an XML to xmlDoc through post, using DOMDocument and XMLHTTP

Set xmlhtp = CreateObject("MSXML2.XMLHTTP.6.0")
Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")

When I recieve the response from the API I write it to a cell to check the response, and it loads there. I find my Nodes and my information. I write it to a cell with

With xmlhtp
.Open "post", sURL, False
.setRequestHeader "Host", "Host-name"
.setRequestHeader "Content-Type", "text/xml;charset=UTF-8"
.setRequestHeader "soapAction", "action-ID"
.send sEnv
xmlDoc.LoadXML .responseText
Worksheets("Ark1").Range("A11").Value = .responseText
MsgBox (xmlDoc.SelectSingleNode("//ExternalReference"))

In my dump to A11 I find the node ExternalReference, and it has info in it, see screenshot image of node ExternalReference with info

But even though everything seems fine, the MsgBox does not find the Node. I get the "Object Variable or With block variable not set"-error message.

I tried to add all xmlns-URLs from the response I get in SoapUI, but that did not help with anything.

I have also tried to loop through the parent node of this node (case) with a sequence I know works in another code I have (after some help yesterday here (not the same script or XML this time though):

How to loop through XML-nodes and validate if values exists?)

The loop I tried to run, it does not produce any content either (seems like it cannot find the nodes in the XML)

Const MAX_AR As Long = 3
Dim Casep, Casec, c As Range, i As Long 
Set c = Worksheets("Ark1").Range("J1")
Set Casep = xmlDoc.getElementsByTagName("Case") 
For Each Casec In Casep                                   
    c.Offset(0, i) = Casec.getElementsByTagName("AccountName")(0).Text 
    c.Offset(1, i) = Casec.getElementsByTagName("ContractName")(0).Text
    c.Offset(2, i) = Casec.getElementsByTagName("ExternalReference")(0).Text
    i = i + 1
    If i >= MAX_AR Then Exit For 'Hopp ut ved oppnådd Max År
Next Casec

It it helps, here is a scrubbed part of the XML:

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
   <s:Header>
      <h:ResponseHeader xmlns:h="URL to schema" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
         <QueueId i:nil="true" xmlns="URL to schema"/>
         <SessionId xmlns="URL to schema">123456789</SessionId>
      </h:ResponseHeader>
   </s:Header>
   <s:Body>
      <CaseGetResponse xmlns="URL to schema">
         <Case xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <AccountName>123456</AccountName>
            <ContractName>123456</ContractName>
            <ExternalReference>extref</ExternalReference>
            <ExternalReference2 i:nil="true"/>
            <ExternalReferences/>
            <ExternalSystemReference i:nil="true"/>
            <Sequence>654321</Sequence>
Solution

  • Adding namespaces fixes the xpath query (but I'm not sure the getElementsByTagName cares about them)

        Dim xmlDoc As New MSXML2.DOMDocument
    Dim cases, cs
    
    'need these next two....
    xmlDoc.setProperty "SelectionLanguage", "XPath"
    xmlDoc.setProperty "SelectionNamespaces", _
                          "xmlns:s='http://schemas.xmlsoap.org/soap/envelope/' " & _
                          "xmlns:i='http://www.w3.org/2001/XMLSchema-instance' " & _
                          "xmlns:xx='http://URL/to/schema' "
    
    xmlDoc.LoadXML Range("A1").Value 'loading from a worksheet...
    
    'this works with the dummy namespace reference
    Debug.Print xmlDoc.DocumentElement.SelectNodes("//xx:ExternalReference")(0).Text '>>extref
    
    'also works
    Set cases = xmlDoc.getElementsByTagName("Case")
    For Each cs In cases
        Debug.Print cs.getElementsByTagName("AccountName")(0).Text
        Debug.Print cs.getElementsByTagName("ContractName")(0).Text
        Debug.Print cs.getElementsByTagName("ExternalReference")(0).Text
    Next cs

    As for why the inherited namespace prefix on <ExternalReference> is xx and not i, see the accepted response here: XML: do child nodes inherit parent's namespace prefix?. Basically prefixed namespaces are not inherited but un-prefixed ones ("default namespaces", declared using xmlns="...") are: in this case <ExternalReference> gets its namespace from <CaseGetResponse> not <Case>

    FYI I was using this XML (note I adjusted your dummy namespace URL):

    <?xml version="1.0"?>
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
   <s:Header>
      <h:ResponseHeader xmlns:h="URL to schema" 
                        xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
         <QueueId i:nil="true" xmlns="URL to schema"/>
         <SessionId xmlns="URL to schema">123456789</SessionId>
      </h:ResponseHeader>
   </s:Header>
   <s:Body>
      <CaseGetResponse xmlns="http://URL/to/schema">
         <Case xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <AccountName>123456</AccountName>
            <ContractName>123456</ContractName>
            <ExternalReference>extref</ExternalReference>
            <ExternalReference2 i:nil="true"/>
            <ExternalReferences/>
            <ExternalSystemReference i:nil="true"/>
            <Sequence>654321</Sequence>
        </Case>
      </CaseGetResponse>
   </s:Body>
</s:Envelope>