Get node values from SOAP response using VBA

Question

I have returned a response from SOAP web services using VBA code. I want to obtain the node values from the response. I have looked across forums for examples but none appear to do exactly what I want. The closest thread which is similar to my situation is the one below:

VBA Excel Macro SelectSingleNode returns nothing

Any examples or assistance on how to get started would be appreciated.

Sample XML Request in VBA:

'Set Reference to Microsoft XML, v6.0
Option Explicit

Dim responseText As String
Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP
Dim xmlDoc As New DOMDocument
Dim webserviceSOAPActionNameSpace

Sub test()

sURL = "http://soap.qacomplete.smartbear.com/psWS.asmx?wsdl"

sEnv = "<?xml version =""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
sEnv = sEnv & "<soap:Body>"
sEnv = sEnv & "<Bugs_LoadByCriteria xmlns=""http://www.pragmaticsw.com/"">"
sEnv = sEnv & "<AuthenticationData>"
sEnv = sEnv & "<AppCode>agSP</AppCode>"
sEnv = sEnv & "<DeptId>81842</DeptId>"
sEnv = sEnv & "<ProjId>92553</ProjId>"
sEnv = sEnv & "<UserId>147280</UserId>"
sEnv = sEnv & "<PassCode>Password1</PassCode>"
sEnv = sEnv & "</AuthenticationData>"
sEnv = sEnv & "<Condition><![CDATA[<Conditions xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"
sEnv = sEnv & " xmlns:xsd='http://www.w3.org/2001/XMLSchema' Operation='opEQU'>"
sEnv = sEnv & "<Items Type='tField'>"
sEnv = sEnv & "<Value xsi:type='xsd:string'>Custom11</Value>"
sEnv = sEnv & "</Items>"
sEnv = sEnv & "<Items Type='tString'>"
sEnv = sEnv & "<Value xsi:type='xsd:string'>Finance</Value>"
sEnv = sEnv & "</Items>"
sEnv = sEnv & "</Conditions>]]>"
sEnv = sEnv & "</Condition>"
sEnv = sEnv & "</Bugs_LoadByCriteria>"
sEnv = sEnv & "</soap:Body>"
sEnv = sEnv & "</soap:Envelope>"

    With xmlhtp

    webserviceSOAPActionNameSpace = "http://www.pragmaticsw.com/"

        .Open "POST", sURL, False

        .setRequestHeader "POST", "http://soap.qacomplete.smartbear.com/psWS.asmx HTTP/1.1"
        .setRequestHeader "Content-Type", "application/soap+xml; charset=UTF-8"
        .setRequestHeader "SOAPAction", webserviceSOAPActionNameSpace & "Bugs_LoadByCriteria"
        .setRequestHeader "Accept-encoding", "zip"

        .send sEnv

        xmlDoc.LoadXML .responseText

End With

End Sub

Sample Response XML:

<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>
      <Bugs_LoadByCriteriaResponse xmlns="http://www.pragmaticsw.com/">
         <Bugs_LoadByCriteriaResult>
            <Bug>
               <CustomFieldNames>
               <BugId>3253017</BugId>
               <Title>DM78 Customer and DM25 Vendor Master Data - default criteria</Title>
               <StatusCode>Closed</StatusCode>
               <SeverityCode>Minor</SeverityCode>
               <PriorityCode>P3</PriorityCode>
               <IssueCode>Data</IssueCode>
               <ResolutionCode>Fixed</ResolutionCode>
               <AssigneeUserId>137784</AssigneeUserId>
               <OpenedBy>136840</OpenedBy>
               <ClosedBy>137748</ClosedBy>
               <ResolvedBy>137748</ResolvedBy>
            </Bug>
         </Bugs_LoadByCriteriaResult>
      </Bugs_LoadByCriteriaResponse>
   </soap:Body>
</soap:Envelope>

So you have something like <soap><response><value>123</value></response></soap> and you're having trouble selecting 123? Please post a sample response so that people can help you find the xpath of the element you require. — Cody Geisler
– Cody Geisler, Commented Jun 8, 2016 at 17:34
Hi Cody - I have added a sample response to the original question — Ami Khan
– Ami Khan, Commented Jun 9, 2016 at 13:16
Error on line 6: The element type "CustomFieldNames" must be terminated by the matching end-tag "</CustomFieldNames>". From an XML schema standpoint I don't think this is allowed and could be why you're finding it hard to work with the repsonse. — Cody Geisler
– Cody Geisler, Commented Jun 9, 2016 at 18:22
My fault - I shortened the response because the actual one is considerably long and I hoped the above would serve as an example. To clarify - I want to use VBA to populate a spreadsheet based on the nodes values provided in the XML response. Are there any useful examples or suggestions to help me with that? — Ami Khan
– Ami Khan, Commented Jun 10, 2016 at 9:32
See community.spiceworks.com/topic/… EChacko2016 Apr 28, 2014 at 12:06 PM --- this is pretty much what I would try. Otherwise, msdn.microsoft.com/en-us/library/aa468547.aspx is a great starting point for a fully developed program, except your soap response will be the DOMDocument (in Particular, look at How Do I Traverse a Document?) — Cody Geisler
– Cody Geisler, Commented Jun 10, 2016 at 12:55

QHarr · Accepted Answer · 2018-06-18 18:44:40Z

Answer by @AmiKhan moved from question to answer.

Option Explicit

'Set Reference to Microsoft XML, v6.0
Dim DefectsCount As Integer
Dim wsDefects As Worksheet
Dim varTargetCycle As String
Dim list As IXMLDOMNodeList
Dim responseText As String
Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.xmlHttp
Dim xmlDoc As New DOMDocument
Dim webserviceSOAPActionNameSpace

Sub GetDefects(PNum, PSize)

    sURL = "http://soap.qacomplete.smartbear.com/psWS.asmx?wsdl"
    varTargetCycle = Range("TargetCycle").Value

    sEnv = "<?xml version =""1.0"" encoding=""utf-8""?>"
    sEnv = sEnv & "<soap:Envelope xmlns:soap=""http://www.w3.org/2003/05/soap-envelope"" xmlns:prag=""http://www.pragmaticsw.com/"">"
    sEnv = sEnv & "<soap:Body>"
    sEnv = sEnv & "<prag:Bugs_LoadByCriteria>"
    sEnv = sEnv & "<prag:AuthenticationData>"
    sEnv = sEnv & "<prag:AppCode>agSP</prag:AppCode>"
    sEnv = sEnv & "<prag:DeptId>81842</prag:DeptId>"
    sEnv = sEnv & "<prag:ProjId>92553</prag:ProjId>"
    sEnv = sEnv & "<prag:UserId>" & V1USER & "</prag:UserId>"
    sEnv = sEnv & "<prag:PassCode>" & V1PASS & "</prag:PassCode>"
    sEnv = sEnv & "</prag:AuthenticationData>"
    sEnv = sEnv & "<prag:Condition><![CDATA[<Conditions xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"
    sEnv = sEnv & " xmlns:xsd='http://www.w3.org/2001/XMLSchema' Operation='opEQU'>"
    sEnv = sEnv & "<Items Type='tField'>"
    sEnv = sEnv & "<Value xsi:type='xsd:string'>Custom17</Value>"
    sEnv = sEnv & "</Items>"
    sEnv = sEnv & "<Items Type='tString'>"
    sEnv = sEnv & "<Value xsi:type='xsd:string'>" & varTargetCycle & "</Value>"
    sEnv = sEnv & "</Items>"
    sEnv = sEnv & "</Conditions>]]></prag:Condition>"

    sEnv = sEnv & "<prag:Sorting>Title</prag:Sorting>"
    sEnv = sEnv & "<prag:PageSize>" & PSize & "</prag:PageSize>"
    sEnv = sEnv & "<prag:PageNumber>" & PNum & "</prag:PageNumber>"

    sEnv = sEnv & "</prag:Bugs_LoadByCriteria>"
    sEnv = sEnv & "</soap:Body>"
    sEnv = sEnv & "</soap:Envelope>"

    With xmlhtp

        webserviceSOAPActionNameSpace = "http://www.pragmaticsw.com/"

        .Open "POST", sURL, False

        .setRequestHeader "POST", "http://soap.qacomplete.smartbear.com/psWS.asmx HTTP/1.1"
        .setRequestHeader "Content-Type", "application/soap+xml; charset=UTF-8"
        .setRequestHeader "SOAPAction", webserviceSOAPActionNameSpace & "Bugs_LoadByCriteria"
        .setRequestHeader "Accept-encoding", "zip"

        .send sEnv

        xmlDoc.LoadXML .responseText
        'MsgBox .responseText
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim Node As IXMLDOMNode
        Dim LRow As Integer

        Set list = xmlDoc.SelectNodes("//Bugs_LoadByCriteriaResponse/Bugs_LoadByCriteriaResult/Bug")

        Set wsDefects = Sheet2

        Dim xmlnodelist As MSXML2.IXMLDOMNodeList
        Dim xnode As MSXML2.IXMLDOMNode

        DefectsCount = list.Length

        With wsDefects

            LRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

            For Each Node In list
                Dim strBugValue As String
                Dim strStatusCode As String
                Dim strSeverity As String
                Dim strPriority As String
                Dim strIssue As String
                Dim strReso As String

                On Error Resume Next             'if null
                strBugValue = Node.SelectSingleNode("BugId").Text
                strStatusCode = Node.SelectSingleNode("StatusCode").Text
                strSeverity = Node.SelectSingleNode("SeverityCode").Text
                strPriority = Node.SelectSingleNode("PriorityCode").Text
                strIssue = Node.SelectSingleNode("IssueCode").Text
                strReso = Node.SelectSingleNode("ResolutionCode").Text

                .Cells(LRow, 1).Value = strBugValue
                .Cells(LRow, 2).Value = strStatusCode
                .Cells(LRow, 3).Value = strSeverity
                .Cells(LRow, 4).Value = strPriority
                .Cells(LRow, 5).Value = strIssue
                .Cells(LRow, 6).Value = strReso

                LRow = LRow + 1
            Next Node
        End With
        Set xmlhtp = Nothing
        Set xmlDoc = Nothing
    End With
End Sub

@amikhan I have moved your answer here i.e. edited out of the question. If you wish to potentially gain rep by posting your own answer here in the answer section leave a note and this will be deleted.

Collectives™ on Stack Overflow

Get node values from SOAP response using VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related