Solved

Parse XML in VB

Posted on 2011-03-15
10
442 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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 500 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

738 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