VBA and SOAP interface

Question

I am trying to connect one MS Access database to a SOAP service.

I was able to connect to the service, retrieve the XML, but I can't read a node value.

This is my code (I replaced some confidential things with ++++++):

Option Compare Database

Private Sub lanzar_XML_Click()
    Dim sUrl As String
    Dim sEnv As String
    Dim xmlhtp As MSXML2.XMLHTTP60
    Dim xmlDoc As MSXML2.DOMDocument60
    Dim limpiartexto As String
    Dim Valoresxml  As MSXML2.IXMLDOMNode

    sUrl = "++++++"
    sEnv = "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:hai=""++++++"">"
    sEnv = sEnv & "   <soapenv:Header/>"
    sEnv = sEnv & "   <soapenv:Body>"
    sEnv = sEnv & "      <hai:QuerySRInfo>"
    sEnv = sEnv & "         <SRNum>EUES160624000378</SRNum>"
    sEnv = sEnv & "         <ServiceCenterId>++++++</ServiceCenterId>"
    sEnv = sEnv & "         <ServiceCenterPW>++++++</ServiceCenterPW>"
    sEnv = sEnv & "      </hai:QuerySRInfo>"
    sEnv = sEnv & "   </soapenv:Body>"
    sEnv = sEnv & "</soapenv:Envelope>"
    Set xmlhtp = New MSXML2.XMLHTTP60
    With xmlhtp
        .Open "POST", sUrl, False
        .setRequestHeader "Content-Type", "text/xml;charset=UTF-8"
        ' .setRequestHeader "SOAPAction:", "rpc/http://www.++++++.com/:QuerySRInfo"
        .setRequestHeader "Content-Length", "Lenght"
        .send sEnv

        Set xmlDoc = New MSXML2.DOMDocument60
        xmlDoc.loadXML .responseText

        limpiartexto = xmlDoc.XML
        limpiartexto = Right(limpiartexto, Len(limpiartexto) - 327)
        limpiartexto = Left(limpiartexto, Len(limpiartexto) - 64)

        Debug.Print limpiartexto
    End With

    xmlDoc.loadXML limpiartexto

    For Each Valoresxml In xmlDoc.getElementsByTagName("ServiceRequests")
        Me.MobilePhone = Valoresxml.selectNodes("MobilePhone")(0).Text
        MsgBox Valoresxml.selectNodes("MobilePhone")(0).Text
    Next
End Sub

I believe my problem is with the format of the XML that is retrieved, instead of a well structured XML, it is just a one-liner containing all nodes.

My debug.print returns:

<ServiceRequests><ServiceRequest xmlns="http://www.siebel.com/xml/SPI%20HET%20ASC%20Query%20Service%20Request"><SrNumber>EUES160624000378</SrNumber><CustomerName>++++++</CustomerName><FirstName>++++++</FirstName><LastName>++++++</LastName><ContactEmail></ContactEmail><MobilePhone>600600600</MobilePhone><HomePhone></HomePhone><WorkPhone></WorkPhone><CustomerGender></CustomerGender><CustomerCoutnry>Spain</CustomerCoutnry><CustomerState></CustomerState><SubmissionTime></SubmissionTime><OfflineSubmissionTime></OfflineSubmissionTime></ServiceRequest></ServiceRequests>

...but I think it should return:

<ServiceRequests>
 <ServiceRequest xmlns="http://www.siebel.com/xml/SPI%20HET%20ASC%20Query%20Service%20Request">    
  <SrNumber>EUES160624000378</SrNumber>
  <CustomerName>++++++</CustomerName>
  <FirstName>++++++</FirstName>
  <LastName>++++++</LastName>
  <ContactEmail></ContactEmail>
  <MobilePhone>600600600</MobilePhone>
  <HomePhone></HomePhone>
  <WorkPhone></WorkPhone>
  <CustomerGender></CustomerGender>
  <CustomerCoutnry>Spain</CustomerCoutnry>
  <CustomerState></CustomerState>
  <SubmissionTime></SubmissionTime>
  <OfflineSubmissionTime></OfflineSubmissionTime>
 </ServiceRequest>
</ServiceRequests>

How can I deal with that?

Note: when I use soapui, the returned XML looks fine.

XML/MSXML doesn't care about line breaks. Is that what the problem is? How is it a problem? — Mathieu Guindon, Aug 18 '16 at 17:14
Possible duplicate of [How to parse XML using vba](http://stackoverflow.com/questions/11305/how-to-parse-xml-using-vba) — trincot, Aug 18 '16 at 17:17
I marked this as a duplicate, as you should not "parse" XML with string functions like `Left` and `Right`. Instead use an DOM API for that. That way that kind of spacing (or lack of it) becomes irrelevant. The API takes care of all that. — trincot, Aug 18 '16 at 17:21
@trincot the interesting part is that OP *is already using MSXML*....... and then works of the XML string. — Mathieu Guindon, Aug 18 '16 at 17:27

score 0 · Answer 1 · answered Aug 18 '16 at 21:00

The response XML has an undeclared namespace that should be declared with any string prefix (below uses doc) to reference underlying elements. Use this prefix in any node path references. Then, iterate using a nodeList. Of course this assumes limpiartexto renders a valid XML after Left() and Right() handling.

Dim XMLNamespaces As String                 ' among other references
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
...
xmlDoc.loadXML limpiartexto
XMLNamespaces = "xmlns:doc='http://www.siebel.com/xml/SPI%20HET%20ASC%20Query%20Service%20Request'"
XmlDoc.setProperty "SelectionNamespaces", XMLNamespaces

Set xmlNodeList = xmlDoc.SelectNodes("//doc:ServiceRequest")

For Each Valoresxml In xmlNodeList
    Me.MobilePhone = Valoresxml.SelectSingleNode("doc:MobilePhone").Text
    MsgBox Valoresxml.SelectSingleNode("doc:MobilePhone").Text
Next Valoresxml

VBA and SOAP interface

1 Answers1