[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Parse XML in VB

Posted on 2011-03-15
10
Medium Priority
?
450 Views
Last Modified: 2012-05-11
I am trying to parse xml data that is in an Access database. When I run this code, I get 2 results, even if the sample data only has it once.

Sub ParseXML(pstrXML As String)
   
    Dim xmlDoc As New MSXML2.DOMDocument
    Dim xmlNode As MSXML2.IXMLDOMElement
    Dim xmlAttrib As MSXML2.IXMLDOMAttribute
     
    xmlDoc.loadXML pstrXML
    For Each xmlNode In xmlDoc.childNodes(0).childNodes      
        If xmlNode.nodeName = "operations" Then
            op = "NumberOfFiles"
            If (Not xmlDoc.selectSingleNode("//reportElement [@reportCode='" & op & "']") Is Nothing) Then
                For Each xmlchild In xmlDoc.selectSingleNode("//reportElement [@reportCode='" & op & "']").parentNode.childNodes
                    Debug.Print xmlchild.getAttribute("reportCode") & ": " & xmlchild.Text
                Next
            End If
      End if                      
    Next xmlNode
 
End Sub
-----------------------------------------------
Sample XML:

        "<operations><operation> " & _
        "<report> " & _
        "<reportElementList> " & _
        "<reportElement type='Info' reportCode='NumberOfFiles' dateTime='12/9/2010 8:06:21 PM'> " & _
        "<reportDataList> " & _
        "<reportData key='Amount'> " & _
        "<value>10</value> " & _
        "</reportData> " & _
        "</reportDataList> " & _
        "</reportElement> " & _
        "<reportElement type='Info' reportCode='NumberOfFolders' dateTime='12/9/2010 8:06:21 PM'> " & _
        "<reportDataList> " & _
        "<reportData key='Amount'> " & _
        "<value>5</value> " & _
        "</reportData> " & _
        "</reportDataList> " & _
        "</reportElement> " & _
        "</reportElementList> " & _
        "</report></operation> " & _
        "</operations></operation>"
------------------------------------------
Results:
NumberOfFiles: 10
NumberOfFolders: 5


Thanks!
0
Comment
Question by:mph23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35136997
Can you clarify your issue? From the code you have posted, and the XML fragment, the results you posted are what I would expect it to produce.
0
 

Author Comment

by:mph23
ID: 35137237
Sorry.

I am trying to figure out why it returned the line:
NumberOfFolders: 5

When my for loop says:
xmlDoc.selectSingleNode("//reportElement [@reportCode='" & op & "']").parentNode.childNodes
where  op = "NumberOfFiles".

Shouldn't it just loop thru the nodes that have reportCode="NumberofFiles"?

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35137321
No. Because your actual line says:

    For Each xmlchild In xmlDoc.selectSingleNode("//reportElement [@reportCode='" & op & "']").parentNode.childNodes

The "parentNode" part takes the selection one step back up the tree to the <reportElementList /> node and then steps through its children. If you just want the one node then remove parentNode.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:mph23
ID: 35137907
How would you recommend I fix the code so that I can look up the values for NumberOfFiles?

ex: NumberofFiles=10

In the xml data, there may be several operation "trees" and so I need to find the NumberOfFiles for each.

Thanks
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35137960
If you just want to grab all the "value" nodes for any "reportElement" with the correct "reportCode" attribute, you could use:
                For Each xmlchild In xmlDoc.selectNodes("//reportElement [@reportCode='" & op & "']//value")
                    Debug.Print xmlchild.Text
                Next

Open in new window

0
 

Author Comment

by:mph23
ID: 35138089
That worked for value, but how do I get the reportcode so I can print:

NumberOfFiles=10
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35138156
You know that already it is stored in the "op" variable.
0
 

Author Comment

by:mph23
ID: 35138215
Yes, but I'm trying to learn how to code nodes so how do I find reportCode in terms of xmlchild?

0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 35138295
OK. In that case you want to start by grabbing a list of the <reportElement /> nodes that match your criteria, since that is the highest node in the tree that has information we need:
For Each xmlchild In xmlDoc.selectNodes("//reportElement [@reportCode='" & op & "']")
Next

Open in new window

Because the <value /> node is nested a few levels down from the <reportElement /> node, it will be easier to make a secondary lookup rather than using nested calls to the "childNodes" collection:
xmlchild.getAttribute("reportCode") & ": " & xmlchild.selectSingleNode("//value").Text

Open in new window

0
 

Author Comment

by:mph23
ID: 35138381
Thank you SO much. The syntax is so confusing to me. The code specified above works so I'll review it further to better understand nodes and attributes.

Thank you!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question