?
Solved

Parse XML in VB

Posted on 2011-03-15
10
Medium Priority
?
446 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

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 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