Solved

Parse XML in VB

Posted on 2011-03-15
10
416 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
  • 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Automating receiving uploaded files via HTTPS instead of SFTP 3 18
Syntax Error in Query 7 30
ms access 2013, running .mdb 2 30
Track name AutoCorrect info 14 47
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now