none
Parse SOAP response in VBA RRS feed

  • Question

  • <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sch="http://www.bmc.com/ctmem/schema800">
       <soapenv:Header/>
       <soapenv:Body>
          <sch:request_aj_rerun>
             <sch:user_token>?</sch:user_token>
             <sch:control_m>BMCTESTSVR</sch:control_m>
             <sch:order_id>0008g</sch:order_id>
          </sch:request_aj_rerun>
       </soapenv:Body>
    </soapenv:Envelope>

    Above is the soap req. I am sending.

    and below is the response back.

    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
       <SOAP-ENV:Body>
          <ctmem:response_poll_aj_rerun xmlns:ctmem="http://www.bmc.com/ctmem/schema800">
             <ctmem:status>OK</ctmem:status>
             <ctmem:control_m>BMCTESTSVR</ctmem:control_m>
             <ctmem:order_id>0008g</ctmem:order_id>
          </ctmem:response_poll_aj_rerun>
       </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

    with few resources available online I am able to send the request but unable to parse the response.

    Private Sub Rerun()
        Dim Req As New MSXML2.XMLHTTP
        Dim sEnv As String
        Dim Resp As New MSXML2.DOMDocument60
        Dim nodesCollection As IXMLDOMSelection
        Dim nodeElement As IXMLDOMElement
        Dim attributes As IXMLDOMNamedNodeMap
        Dim attr As IXMLDOMAttribute
        Req.Open "Post", "http://TEST:8080/ctmbpi/TESTENDPOINT", False
        
     ' we create our SOAP envelope for submission to the Web Service
         sEnv = sEnv & "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:sch=""http://www.bmc.com/ctmem/schema800"">"
         sEnv = sEnv & "  <soapenv:Header/>"
         sEnv = sEnv & "  <soapenv:Body>"
         sEnv = sEnv & "   <sch:request_aj_rerun>"
         sEnv = sEnv & "    <sch:user_token>?</sch:user_token>"
         sEnv = sEnv & "    <sch:control_m>BMCTESTSVR</sch:control_m>"
         sEnv = sEnv & "   <sch:order_id>0008g</sch:order_id>"
         sEnv = sEnv & "   </sch:request_aj_rerun>"
         sEnv = sEnv & "  </soapenv:Body>"
         sEnv = sEnv & "</soapenv:Envelope>"
    ' Send SOAP Request
        Req.send (sEnv)
    
    ' Display results in MessageBox
        MsgBox Req.responseText
        Resp.LoadXML (Req.responseText)
        Resp.setProperty "SelectionNamespaces", "xmlns:wsdl='http://schemas.xmlsoap.org/wsdl/' xmlns:SOAP-ENV='http://schemas.xmlsoap.org/soap/envelope/' xmlns:soap='http://schemas.xmlsoap.org/wsdl/soap/' xmlns='http://www.bmc.com/ctmem/schema800' xmlns:ctmem='http://www.bmc.com/ctmem/schema800'"
    
        Set nodesCollection = Resp.SelectNodes("//SOAP-ENV:Envelope/SOAP-ENV:Body/ctmem:response_poll_aj_rerun/ctmem:status")
        
        For Each nodeElement In nodesCollection
        Dim str As String
        For Each attr In nodeElement.attributes ' here it returns empty for somereason
          str = str & "|" & attr.nodeName & "=" & attr.NodeValue
        Next
        MsgBox str
    Next
        
      'clean up code
        Set Req = Nothing
        Set Resp = Nothing
        
        
    End Sub

    Thanks.

    Wednesday, November 25, 2015 7:48 PM

All replies

  • Maybe it's too late to answer, but i happened using this method and get it works, i used most of the method above except made few changes when i set new object

    the modification i made is as below

    Set xmlHtp = CreateObject("MSXML2.XMLHTTP") 'Set XMLDOC = CreateObject("MSXML2.DOMDocument.6.0") 'this is wrong, I'm using windows10 + excel2016 and it returns nothing Set XMLDOC = CreateObject("MSXML2.DOMDocument") 'this is fine


    Saturday, March 10, 2018 3:38 AM