XML SOAP Parse VBA

Question

I have an XML API response which I am trying to parse in VBA. I am trying to use

objXML.selectNodes("//soap:Envelope/soap:Body/ConsignmentTrackingGetFullDetailsV3Response/ConsignmentTrackingGetFullDetailsV3Result/FullConsignmentDetails/ConsignmentStatuses")

but this doesn't appear to find any nodes.

I have also looked at the objXML.getElementsByTagName but this doesn't work either

I am able to access these nodes by using a number of childnodes but this doesn't seem very efficient. I am new to SOAP but have tried to add namespaces.

The XML response is below.

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <ConsignmentTrackingGetFullDetailsV3Response xmlns="http://webapp-cl.internet-delivery.com/ThirdPartyIntegrationService">
         <ConsignmentTrackingGetFullDetailsV3Result>
            <ResultState>Successful</ResultState>
            <FullConsignmentDetails>
               <ConsignmentNumber>31487490001622</ConsignmentNumber>
               <ConsignmentStatuses>
                  <GetConsignmentDetailsStatus>
                     <StatusCode>2</StatusCode>
                     <StatusDescription>Collected</StatusDescription>
                  </GetConsignmentDetailsStatus>
                  <GetConsignmentDetailsStatus>
                     <StatusCode>4</StatusCode>
                     <StatusDescription>Out For Delivery</StatusDescription>
                  </GetConsignmentDetailsStatus>
               </ConsignmentStatuses>
            </FullConsignmentDetails>
         </ConsignmentTrackingGetFullDetailsV3Result>
      </ConsignmentTrackingGetFullDetailsV3Response>
   </soap:Body>
</soap:Envelope>

Any suggestions on how to efficiently parse the required nodes would be appreciated.

Did you try the below script? What's your feedback?

SIM
– SIM

2018-01-04 06:47:59 +00:00
Commented Jan 4, 2018 at 6:47 — SIM
– SIM, Commented Jan 4, 2018 at 6:47

SIM · Accepted Answer · 2018-01-03 18:43:35Z

1

Try this. It should work. I just tested locally.

Sub TestXML2()
    Dim http As New XMLHTTP60
    Dim xmldoc As Object, post As Object

    With http
        .Open "GET", "place_url_here", False
        .send
        Set xmldoc = CreateObject("MSXML2.DOMDocument")
        xmldoc.LoadXML .responseXML.XML
    End With

    For Each post In xmldoc.SelectNodes("//FullConsignmentDetails")
        r = r + 1: Cells(r, 1) = post.SelectNodes(".//ConsignmentNumber")(0).Text
        Cells(r, 2) = post.SelectNodes(".//StatusCode")(0).Text
        Cells(r, 3) = post.SelectNodes(".//StatusDescription")(0).Text
    Next post

    Set xmldoc = Nothing
End Sub

Reference to add to the library:

Microsoft XML, v6.0

answered Jan 3, 2018 at 18:43

SIM

22.5k6 gold badges45 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TGChris Over a year ago

This has worked great, much simpler than my previous solution!

Collectives™ on Stack Overflow

XML SOAP Parse VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related