VBA Excel Macro SelectSingleNode returns nothing

Question

My very first venture with excel macro, I have written code to parse a web service response and I would like to update tag values in individual cells in excel.

Following is the excerpt of my XML (a huge web service response)

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
   <SOAP-ENV:Header xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"/>
   <soap:Body>
      <bm:getTransactionResponse xmlns:bm="http://xmlns.oracle.com/XXXCLD/commerce/ZZZYYY_PPP_1">
         <bm:status>
            <bm:success>true</bm:success>
            <bm:message>Wed Apr 06 09:04:32 UTC 2016 - Successfully processed API for test1</bm:message>
         </bm:status>
         <bm:transaction>
            <bm:category>data</bm:category>
            <bm:action>add</bm:action>
            <bm:id>1111</bm:id>
            <bm:process_var_name>xvgfdr</bm:process_var_name>
            <bm:buyer_company_name>test1</bm:buyer_company_name>
            <bm:supplier_company_name>test1</bm:supplier_company_name>
            <bm:step_var_name>waitingForInternalApproval</bm:step_var_name>
            <bm:last_document_number>2</bm:last_document_number>
            <bm:date_added>2016-04-04 12:14:57</bm:date_added>
            <bm:date_modified>2016-04-06 09:04:18</bm:date_modified>
            <bm:data_xml>
               <bm:transaction bm:bs_id="11111" bm:buyer_company_name="test1" bm:buyer_user_name="someone" bm:currency_pref="GBP" bm:data_type="0" bm:document_name="Transaction" bm:document_number="1" bm:document_var_name="transaction" bm:process_var_name="XXX_1" bm:supplier_company_name="test1">
                  <bm:_document_number>1</bm:_document_number>
                  <bm:createdBy_t>SomeOne</bm:createdBy_t>
                  <bm:_price_book_var_name>_default</bm:_price_book_var_name>
                  <bm:createdDate_t>2016-04-04 00:00:00</bm:createdDate_t>
                  <bm:currency_t>INR</bm:currency_t>
                  <bm:_customer_t_first_name/>
                  <bm:_customer_t_last_name/>
                  <bm:_customer_t_company_name>Test Account</bm:_customer_t_company_name>

I am trying to fetch the Value of Tag <bm:_customer_t_company_name>

Below is the code, I've been using.

Sub Button1_Click()

                'Set and instantiate our working objects
                    Dim Req As Object
                    Dim sEnv As String
                    Dim Resp As New MSXML2.DOMDocument60
                    Set Req = CreateObject("MSXML2.XMLHTTP")
                    Set Resp = CreateObject("MSXML2.DOMDocument.6.0")                   
                    With Req

                    .Open "Post", "https://XXXX.com/", False                    
                    Dim Pwd As String
                    Pwd = Range("D8").Value


                    Dim QuoteId As String
                    QuoteId = Range("D9").Value

                    ' SOAP envelope for submission to the Web Service
                     sEnv = sEnv & "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"">"
                     sEnv = sEnv & "  <soapenv:Header>"
                     sEnv = sEnv & "  <wsse:Security xmlns:wsse=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"" xmlns:wsu=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"">"
                     sEnv = sEnv & "  <wsse:UsernameToken wsu:Id=""UsernameToken-2"">"
                     sEnv = sEnv & "  <wsse:Username>" & Range("D7").Value & "</wsse:Username>"
                     sEnv = sEnv & "  <wsse:Password Type=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText"">" & Pwd & "</wsse:Password>"
                     sEnv = sEnv & "  </wsse:UsernameToken>"
                     sEnv = sEnv & "  </wsse:Security>"
                     sEnv = sEnv & "  </soapenv:Header>"
                     sEnv = sEnv & "  <soapenv:Body>"
                     sEnv = sEnv & "   <bm:getTransaction>"
                     sEnv = sEnv & "    <bm:transaction>"
                     sEnv = sEnv & "    <bm:id>" & Range("D9").Value & "</bm:id>"
                     sEnv = sEnv & "   </bm:transaction>"
                     sEnv = sEnv & "   </bm:getTransaction>"
                     sEnv = sEnv & "  </soapenv:Body>"
                     sEnv = sEnv & "</soapenv:Envelope>"                    
                    ' Send SOAP Request
        .send (sEnv)       

        Resp.LoadXML Req.responseText

        End With
        If Resp Is Nothing Then
            MsgBox "No XML"
        End If
        With Resp

        .setProperty "SelectionNamespaces", "xmlns:bm=""http://xmlns.oracle.com/XXXCLD/commerce/ZZZYYY_PPP_1"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"""
        Dim strName As String
        If .SelectSingleNode("//bm:_customer_t_company_name") Is Nothing Then
            MsgBox "No Node"
        End If
        strName = .SelectSingleNode("//bm:_customer_t_company_name").Text
        MsgBox strName
    End With

  'clean up code
    Set Req = Nothing
    Set Resp = Nothing
Range("A1").Value = "DONE"
End Sub

.SelectSingleNode("//soap:Body") works fine. the .SelectSingleNode("//bm:_customer_t_company_name") always returns Nothing when I test my code. So is the

.SelectSingleNode("//bm:getTransactionResponse"), returns nothing.

Can you please let me know what I am doing wrong ?

Here is a screenshot of full XML structureenter image description here

Answer 1

Nothing's wrong with the code as you presented it. You should examine the XML that the web service is actually returning to make sure that it has the tag that you're looking for.

I reduced your example to the following VBA Sub, which runs without error.

Sub Test()
    Dim xml
    Set xml = CreateObject("MSXML2.DOMDocument.6.0")
    xml.LoadXML "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/""> <SOAP-ENV:Header xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/""/> <soap:Body> <bm:getTransactionResponse xmlns:bm=""http://xmlns.oracle.com/XXXCLD/commerce/ZZZYYY_PPP_1""> <bm:status> <bm:success>true</bm:success> <bm:message>Wed Apr 06 09:04:32 UTC 2016 - Successfully processed API for test1</bm:message> </bm:status> <bm:transaction> <bm:category>data</bm:category> <bm:action>add</bm:action> <bm:id>1111</bm:id> <bm:process_var_name>xvgfdr</bm:process_var_name> <bm:buyer_company_name>test1</bm:buyer_company_name> <bm:supplier_company_name>test1</bm:supplier_company_name> <bm:step_var_name>waitingForInternalApproval</bm:step_var_name> <bm:last_document_number>2</bm:last_document_number> <bm:date_added>2016-04-04 12:14:57</bm:date_added> <bm:date_modified>2016-04-06 09:04:18</bm:date_modified> <bm:data_xml> <bm:transaction bm:bs_id=""11111"" bm:buyer_company_name=""test1"" bm:buyer_user_name=""someone"" bm:currency_pref=""GBP"" bm:data_type=""0"" bm:document_name=""Transaction"" bm:document_number=""1"" bm:document_var_name=""transaction"" bm:process_var_name=""XXX_1"" bm:supplier_company_name=""test1""> <bm:_document_number>1</bm:_document_number> <bm:createdBy_t>SomeOne</bm:createdBy_t> <bm:_price_book_var_name>_default</bm:_price_book_var_name> <bm:createdDate_t>2016-04-04 00:00:00</bm:createdDate_t> <bm:currency_t>INR</bm:currency_t> <bm:_customer_t_first_name/> <bm:_customer_t_last_name/> <bm:_customer_t_company_name>Test Account</bm:_customer_t_company_name> </bm:transaction> </bm:data_xml> </bm:transaction> </bm:getTransactionResponse> </soap:Body> </soap:Envelope>"

    xml.SetProperty "SelectionNamespaces", "xmlns:bm=""http://xmlns.oracle.com/XXXCLD/commerce/ZZZYYY_PPP_1"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"""

    Debug.Print xml.SelectSingleNode("//bm:_customer_t_company_name").Text
End Sub

Output is Test Account.