Parse SOAP XML Result (VBA / MSXML 6)

Good day,

Environment: Excel 2007, MSXML 6.0, Sharepoint 3

I would like to parse a Sharepoint SOAP response which is returned after creating new rows in a Sharepoint list. I do however not get to the elements I wish to. I am sure I do overlook something obvious.

I would like to return:

1) The ID of the result - this is causing issues 2) Result Error Number - works accessing the node 3) Result Error Text (if existing) - works accessing the node

ID 1 is a success response, ID 2 is a failure response. I am also wondering why ID 1 has a closing ID tag which ID 2 has not and if this may causes problems.

This is what I have so far:

Sub Parse_Soap_Response()

Dim xml_soap_response As String

Dim xml_document As Object 'New MSXML2.DOMDocument60
Dim xml_nodes_collection As Variant 'IXMLDOMSelection
Dim xml_node_element As Variant 'IXMLDOMElement
Dim xml_node_attributes As Variant 'IXMLDOMNamedNodeMap
Dim xml_node_attribute As Variant 'IXMLDOMAttribute

Set xml_document = CreateObject("MSXML2.DOMDocument.6.0")

'Set XML opening options
With xml_document
  .Async = False
  .PreserveWhiteSpace = False
  .ValidateOnParse = False
  .ResolveExternals = False
  'Use full XPath functionality
  .SetProperty "SelectionLanguage", "XPath"
  'Add specific Namespaces to work with Paths
  .SetProperty "SelectionNamespaces", "xmlns:soap=""http://www.w3.org/2003/05/soap-envelope"" " & _
                                      "xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" " & _
                                      "xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" " & _
                                      "xmlns=""http://schemas.microsoft.com/sharepoint/soap/"" " & _
                                      "xmlns:rs=""urn:schemas-microsoft-com:rowset"" " & _
                                      "xmlns:z=""#RowsetSchema"""

End With



xml_soap_response = _
"<?xml version=""1.0"" encoding=""utf-8""?> " & _
"<soap:Envelope  " & _
"    xmlns:soap=""http://www.w3.org/2003/05/soap-envelope""  " & _
"    xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""  " & _
"    xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">  " & _
"    <soap:Body>  " & _
"        <UpdateListItemsResponse  " & _
"            xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">  " & _
"            <UpdateListItemsResult>  " & _
"                <Results>  " & _
"                    <Result ID=""1,New"">  " & _
"                        <ErrorCode>0x00000000</ErrorCode><ID />  " & _
"                        <z:row ows_Title=""Direct Access Test""  " & _
"                         xmlns:z=""#RowsetSchema"" />  " & _
"                    </Result> " & _
"                    <Result ID=""2,New""> " & _
"                        <ErrorCode>0x81020014</ErrorCode> " & _
"                        <ErrorText>One or more field types are not installed properly. Go to the list settings page to delete these fields.</ErrorText> " & _
"                    </Result> " & _
"                </Results> " & _
"            </UpdateListItemsResult> " & _
"        </UpdateListItemsResponse> " & _
"    </soap:Body> " & _
"</soap:Envelope>"

'Load XML File and report Error if any
If Not xml_document.LoadXML(xml_soap_response) Then

    MsgBox "Error while loading XML File:" & vbCrLf & vbCrLf & _
      "Line Number " & xml_document.parseError.Line & vbCrLf & _
      xml_document.parseError.reason & " (" & xml_document.parseError.ErrorCode & ")", vbCritical, "Error"

    Exit Sub

End If


Set xml_nodes_collection = xml_document.SelectNodes("//soap:Envelope/soap:Body/UpdateListItemsResponse/UpdateListItemsResult/Results")

'Go through all nodes
For Each xml_node_element In xml_nodes_collection

    'Go through all attributes
    For Each xml_node_attribute In xml_node_element.attributes

      'Should return
      '1) The ID of the result
      '2) Result Error Number
      '3) Result Error Text
      Debug.Print xml_node_attribute.nodename & "=" & xml_node_attribute.NodeValue

    Next

Next


'Close the xml document
Set xml_document = Nothing


End Sub

Thanks in advance for any help.

Answers


Issue resolved meanwhile:

To work with XPATH, all namespaces must get a prefix. If missing in the XML one must be invented within the XML opening options of MSXML (here prefix "sp" is invented):

Then all attributes and child nodes can be accessed properly.

xml_document.SetProperty "SelectionNamespaces", "xmlns:sp=""http://schemas.microsoft.com/sharepoint/soap/""

'Set the start path
Set xml_nodes_collection = xml_document.SelectNodes("//soap:Envelope/soap:Body/sp:UpdateListItemsResponse/sp:UpdateListItemsResult/sp:Results/sp:Result")

'Go through all row IDs
For Each xml_node_element In xml_nodes_collection
   row_id = xml_node_element.attributes(0).NodeValue

   For Each xml_child_node In xml_node_element.ChildNodes
       '.......
   Next
Next

Need Your Help

Matlab fopen command responds to string but not variable equaling same string

matlab io fopen

I'm wondering if anyone can shed some light on the following issue with Matlab fopen command:

Free or trial Oracle database environment

oracle toad oracle-xe

What is the best free (or trial) Oracle database development environment I can have?