title: VBA Excel Macro SelectSingleNode returns nothing
body: <blockquote>
    <p>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.</p>
    
    <p>Following is the excerpt of my XML (a huge web service response)</p>
  </blockquote>
  
  <pre><code>&lt;soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"&gt;
     &lt;SOAP-ENV:Header xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"/&gt;
     &lt;soap:Body&gt;
        &lt;bm:getTransactionResponse xmlns:bm="http://xmlns.oracle.com/XXXCLD/commerce/ZZZYYY_PPP_1"&gt;
           &lt;bm:status&gt;
              &lt;bm:success&gt;true&lt;/bm:success&gt;
              &lt;bm:message&gt;Wed Apr 06 09:04:32 UTC 2016 - Successfully processed API for test1&lt;/bm:message&gt;
           &lt;/bm:status&gt;
           &lt;bm:transaction&gt;
              &lt;bm:category&gt;data&lt;/bm:category&gt;
              &lt;bm:action&gt;add&lt;/bm:action&gt;
              &lt;bm:id&gt;1111&lt;/bm:id&gt;
              &lt;bm:process_var_name&gt;xvgfdr&lt;/bm:process_var_name&gt;
              &lt;bm:buyer_company_name&gt;test1&lt;/bm:buyer_company_name&gt;
              &lt;bm:supplier_company_name&gt;test1&lt;/bm:supplier_company_name&gt;
              &lt;bm:step_var_name&gt;waitingForInternalApproval&lt;/bm:step_var_name&gt;
              &lt;bm:last_document_number&gt;2&lt;/bm:last_document_number&gt;
              &lt;bm:date_added&gt;2016-04-04 12:14:57&lt;/bm:date_added&gt;
              &lt;bm:date_modified&gt;2016-04-06 09:04:18&lt;/bm:date_modified&gt;
              &lt;bm:data_xml&gt;
                 &lt;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"&gt;
                    &lt;bm:_document_number&gt;1&lt;/bm:_document_number&gt;
                    &lt;bm:createdBy_t&gt;SomeOne&lt;/bm:createdBy_t&gt;
                    &lt;bm:_price_book_var_name&gt;_default&lt;/bm:_price_book_var_name&gt;
                    &lt;bm:createdDate_t&gt;2016-04-04 00:00:00&lt;/bm:createdDate_t&gt;
                    &lt;bm:currency_t&gt;INR&lt;/bm:currency_t&gt;
                    &lt;bm:_customer_t_first_name/&gt;
                    &lt;bm:_customer_t_last_name/&gt;
                    &lt;bm:_customer_t_company_name&gt;Test Account&lt;/bm:_customer_t_company_name&gt;
  </code></pre>
  
  <p>I am trying to fetch the  Value of Tag <code>&lt;bm:_customer_t_company_name&gt;</code></p>
  
  <blockquote>
    <p>Below is the code, I've been using.             </p>
  </blockquote>
  
  <pre><code>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 &amp; "&lt;soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/""&gt;"
                       sEnv = sEnv &amp; "  &lt;soapenv:Header&gt;"
                       sEnv = sEnv &amp; "  &lt;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""&gt;"
                       sEnv = sEnv &amp; "  &lt;wsse:UsernameToken wsu:Id=""UsernameToken-2""&gt;"
                       sEnv = sEnv &amp; "  &lt;wsse:Username&gt;" &amp; Range("D7").Value &amp; "&lt;/wsse:Username&gt;"
                       sEnv = sEnv &amp; "  &lt;wsse:Password Type=""http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText""&gt;" &amp; Pwd &amp; "&lt;/wsse:Password&gt;"
                       sEnv = sEnv &amp; "  &lt;/wsse:UsernameToken&gt;"
                       sEnv = sEnv &amp; "  &lt;/wsse:Security&gt;"
                       sEnv = sEnv &amp; "  &lt;/soapenv:Header&gt;"
                       sEnv = sEnv &amp; "  &lt;soapenv:Body&gt;"
                       sEnv = sEnv &amp; "   &lt;bm:getTransaction&gt;"
                       sEnv = sEnv &amp; "    &lt;bm:transaction&gt;"
                       sEnv = sEnv &amp; "    &lt;bm:id&gt;" &amp; Range("D9").Value &amp; "&lt;/bm:id&gt;"
                       sEnv = sEnv &amp; "   &lt;/bm:transaction&gt;"
                       sEnv = sEnv &amp; "   &lt;/bm:getTransaction&gt;"
                       sEnv = sEnv &amp; "  &lt;/soapenv:Body&gt;"
                       sEnv = sEnv &amp; "&lt;/soapenv:Envelope&gt;"                    
                      ' 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
  </code></pre>
  
  <blockquote>
    <p>.SelectSingleNode("//soap:Body") works fine.
                     the .SelectSingleNode("//bm:_customer_t_company_name") always returns Nothing when I test my code. So is the </p>
    
    <blockquote>
      <p>.SelectSingleNode("//bm:getTransactionResponse"), returns nothing.</p>
      
      <p>Can you please let me know what I am doing wrong ?</p>
    </blockquote>
  </blockquote>
  
  <p>Here is a screenshot of full XML structure<a href="http://i.stack.imgur.com/26aFK.jpg" rel="nofollow">enter image description here</a></p>