none
Parse SOAP XML Result RRS feed

  • Question

  • 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
    3) Result Error Text (if existing)

    ID 1 is a success response, ID 2 is a failure response. I am also wondering why ID 1 has <ID /> 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.


    Philipp Post


    Saturday, February 1, 2014 6:24 PM

All replies

  • 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



    Philipp Post

    Tuesday, February 4, 2014 5:12 PM