Solved

Parse XML in VB

Posted on 2011-03-15
10
445 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
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

696 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