none
VBA & parsing XML response from XMLHttp request RRS feed

  • Question

  • I am trying to parse an xml response from a office sharepoint 2007 web service to get list items from a list and I am having trouble getting VBA to select just the row data from the list

    this is the xml response

    <?xml version="1.0" encoding="utf-8"?>
    <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>
    	<GetListItemsResponse xmlns="http://schemas.microsoft.com/sharepoint/soap/">
    		<GetListItemsResult>
    			<listitems xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
       				xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
       				xmlns:rs='urn:schemas-microsoft-com:rowset'
       				xmlns:z='#RowsetSchema'>
    				<rs:data ItemCount="2">
      					<z:row ows_Attachments='1' ows_LinkTitle='Update the Summary Spreadsheet' ows_Modified='2008-06-27 12:56:51' ows_MetaInfo='4;#' ows__ModerationStatus='0' ows__Level='1' ows_Title='Update the Summary Spreadsheet' ows_ID='4' ows_owshiddenversion='2' ows_UniqueId='4;#{5096E14F-0F8B-4CC5-BAD6-47A0371950C9}' ows_FSObjType='4;#0' ows_Created_x0020_Date='4;#2009-03-09 08:03:41' ows_Created='2008-06-27 12:56:51' ows_FileLeafRef='4;#4_.000' ows_FileRef='4;#areas/it/pmo/Lists/Announcements/4_.000' />
      					<z:row ows_Attachments='0' ows_LinkTitle='Vote for your favorite project...' ows_Modified='2008-06-20 16:21:55' ows_MetaInfo='3;#' ows__ModerationStatus='0' ows__Level='1' ows_Title='Vote for your favorite project...' ows_ID='3' ows_owshiddenversion='1' ows_UniqueId='3;#{DCD96605-8AE9-4870-AD41-5107B15F8786}' ows_FSObjType='3;#0' ows_Created_x0020_Date='3;#2009-03-09 08:03:41' ows_Created='2008-06-20 16:21:55' ows_FileLeafRef='3;#3_.000' ows_FileRef='3;#areas/it/pmo/Lists/Announcements/3_.000' />
    				</rs:data>
    
    			</listitems>
    		</GetListItemsResult>
    	</GetListItemsResponse>
    </soap:Body>
    </soap:Envelope>
    
    

    And this is my VBA code

     

    If xmlhttp.Status = "200" Then
          'MsgBox ("response" + xmlhttp.responseXML.XML)
          
          ThisDocument.Content = xmlhttp.responseXML.XML
          
          xmlResponse = xmlhttp.responseXML.XML
        
          Dim o
          
          Set o = CreateObject("MSXML2.DOMDocument.6.0")
          
          o.LoadXML (xmlResponse)
          
          ns = "xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"""
          ns2 = "xmlns=""http://schemas.microsoft.com/sharepoint/soap/"""
          ns3 = "xmlns:rs=""urn:schemas-microsoft-com:rowset"""
          ns4 = "xmlns:z=""#RowsetSchema"""
          
      
          
          o.setProperty "SelectionNamespaces", ns & " " & ns2 & " " & ns3 & " " & ns4
          
          Dim myNode
          
          Set myNode = o.SelectNodes("//soap:Envelope/soap:Body/GetListItemsResponse/GetListItemsResult/listitems/rs:data/z:row")
          
          
      End If
    
    Everything works fine except the o.SelectNodes line. All I want to get is the z:row nodes so that I can loop through them and grab the attributes out of.

     

    • Moved by Liliane Teng Tuesday, March 1, 2011 2:02 AM (From:Visual Basic General)
    Monday, February 28, 2011 5:16 PM

Answers

  • Hi Zenuke,

    Have you resolved your issue yet? Do the suggestions work for you or not? I coded the following code snippet which can achieve your goal. You should add the reference Microsoft XML, v6.0 and I use Office 2010.

    Private Sub CommandButton1_Click()

    Dim oXML As Object
    Set oXML = CreateObject("MSXML.DOMDocument")
    oXML.Load "1.xml"
         
    ns = "xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"""
    ns2 = "xmlns=""http://schemas.microsoft.com/sharepoint/soap/"""
    ns3 = "xmlns:rs=""urn:schemas-microsoft-com:rowset"""
    ns4 = "xmlns:z=""#RowsetSchema"""
         
    oXML.setProperty "SelectionNamespaces", ns & " " & ns2 & " " & ns3 & " " & ns4

    Dim nodesCollection As IXMLDOMSelection
    Dim nodeElement As IXMLDOMElement
    Dim attributes As IXMLDOMNamedNodeMap
    Dim attr As IXMLDOMAttribute
         
    Set nodesCollection = oXML.SelectNodes("//soap:Envelope/soap:Body/GetListItemsResponse/GetListItemsResult/listitems/rs:data/z:row")
    For Each nodeElement In nodesCollection
        Dim str As String
        For Each attr In nodeElement.attributes
          str = str & "|" & attr.nodeName & "=" & attr.NodeValue
        Next
        MsgBox str
    Next

    End Sub

    I hope it can help you and feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Bruce Song Thursday, March 3, 2011 4:36 AM
    • Marked as answer by Bruce Song Wednesday, March 9, 2011 2:03 AM
    Thursday, March 3, 2011 4:35 AM
  • FWIW as long as you know that there can be no other z:row elements (and I doubt if there would be in this kind of structure), then you can probably simplify the selection statement in Bruce Song's code from

    Set nodesCollection = oXML.SelectNodes("//soap:Envelope/soap:Body/GetListItemsResponse/GetListItemsResult/listitems/rs:data/z:row")

    to

    Set nodesCollection = oXML.SelectNodes("//z:row")



    Peter Jamieson
    • Proposed as answer by Bruce Song Friday, March 4, 2011 2:30 AM
    • Marked as answer by Bruce Song Wednesday, March 9, 2011 2:03 AM
    Friday, March 4, 2011 1:21 AM

All replies

  • You may want to post to the VBA forum, or perhaps a moderator can move your question there:

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/threads

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, February 28, 2011 6:09 PM
  • Umm... it is in the section.... thanks for playing.
    Monday, February 28, 2011 6:19 PM
  • Well you can't really do it like that because the node you're searching for is nested.

    You'll have to loop through the selectednodes of /soap:Envelope/soap:Body/GetListItemsResponse/GetListItemsResult/listitems/rs:data

    Adam


    Ctrl+Z
    Monday, February 28, 2011 6:26 PM
  • what is wrong is if I try to use that as a SelectedNodes i don't actually get the correct node it seems.
    • Edited by Zenuke Monday, February 28, 2011 6:50 PM
    Monday, February 28, 2011 6:45 PM
  • You will have to check the xpath code for VBA. I don't know off hand: http://msdn.microsoft.com/en-us/library/aa174735(v=office.11).aspx

    Adam


    Ctrl+Z
    Monday, February 28, 2011 6:48 PM
  • Umm... it is in the section.... thanks for playing.

    The title isn't specific but the forum is for Visual Basic .NET related questions.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, February 28, 2011 6:51 PM
  • Paul is correct. Your issue is with VBA XPath not .net

    Adam


    Ctrl+Z
    Monday, February 28, 2011 6:58 PM
  • seems like it start to fail once I hit /soap:Envelope/soap:Body/GetListItemsResponse

    fine before that.

    Monday, February 28, 2011 6:59 PM
  • Hello Zenuke,

    Thanks for your post.

    I have moved this thread to VBA forum where you may get more better support because more this aspect experts live there. Thanks for your understanding.

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 1, 2011 2:03 AM
  • Hi Zenuke,

    Good question. Until this moment, the only way I've found to access subnodes in a XML DomDocument is actively setting subnodes into variables and taking subnodes from it.

     

    Example:

     

        Dim oXMLDoc as DOMDocument
    
        Dim oXMLNode1 as IXMLDOMNode
    
        Dim oXMLNode2 as IXMLDOMNode
    
        Dim oXMLNode3 as IXMLDOMNode
    
    	
    
        oXMLDoc.LoadXML (xmlResponse)
    
        Set oXMLNode1 = oXMLDoc.childNodes(1)
    
        Set oXMLNode2 = GetXMLNodeByAttribute(oXMLNode1.childNodes, "name", "Node1 Name")
    
        Set oXMLNode3 = GetXMLNodeByAttribute(oXMLNode2.childNodes, "name", "Node2 Name")
    

     

    Hope someone has a better way to access directly a specific subnode.


    Tiago Cardoso VB / VBA Analyst
    • Proposed as answer by Bruce Song Thursday, March 3, 2011 4:36 AM
    Tuesday, March 1, 2011 4:06 AM
  • Hi Zenuke,

    Have you resolved your issue yet? Do the suggestions work for you or not? I coded the following code snippet which can achieve your goal. You should add the reference Microsoft XML, v6.0 and I use Office 2010.

    Private Sub CommandButton1_Click()

    Dim oXML As Object
    Set oXML = CreateObject("MSXML.DOMDocument")
    oXML.Load "1.xml"
         
    ns = "xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"""
    ns2 = "xmlns=""http://schemas.microsoft.com/sharepoint/soap/"""
    ns3 = "xmlns:rs=""urn:schemas-microsoft-com:rowset"""
    ns4 = "xmlns:z=""#RowsetSchema"""
         
    oXML.setProperty "SelectionNamespaces", ns & " " & ns2 & " " & ns3 & " " & ns4

    Dim nodesCollection As IXMLDOMSelection
    Dim nodeElement As IXMLDOMElement
    Dim attributes As IXMLDOMNamedNodeMap
    Dim attr As IXMLDOMAttribute
         
    Set nodesCollection = oXML.SelectNodes("//soap:Envelope/soap:Body/GetListItemsResponse/GetListItemsResult/listitems/rs:data/z:row")
    For Each nodeElement In nodesCollection
        Dim str As String
        For Each attr In nodeElement.attributes
          str = str & "|" & attr.nodeName & "=" & attr.NodeValue
        Next
        MsgBox str
    Next

    End Sub

    I hope it can help you and feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Bruce Song Thursday, March 3, 2011 4:36 AM
    • Marked as answer by Bruce Song Wednesday, March 9, 2011 2:03 AM
    Thursday, March 3, 2011 4:35 AM
  • FWIW as long as you know that there can be no other z:row elements (and I doubt if there would be in this kind of structure), then you can probably simplify the selection statement in Bruce Song's code from

    Set nodesCollection = oXML.SelectNodes("//soap:Envelope/soap:Body/GetListItemsResponse/GetListItemsResult/listitems/rs:data/z:row")

    to

    Set nodesCollection = oXML.SelectNodes("//z:row")



    Peter Jamieson
    • Proposed as answer by Bruce Song Friday, March 4, 2011 2:30 AM
    • Marked as answer by Bruce Song Wednesday, March 9, 2011 2:03 AM
    Friday, March 4, 2011 1:21 AM