Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Looping through XML Data

Posted on 2006-06-20
10
Medium Priority
?
375 Views
Last Modified: 2010-04-07
Hello XML'ers
Got a question today.  I have an XML File that i am writing a vba application to manipulate.  I am having difficulty traversing the childnodes.
Here is the xml AND THE VBA.
Thanks Ahead of time,
Dim myXML As New MSXML2.DOMDocument
    Dim myXElem As MSXML2.IXMLDOMElement
    Dim myXRecord As MSXML2.IXMLDOMElement
    Dim myXField As MSXML2.IXMLDOMElement
    Dim myXEntry As MSXML2.IXMLDOMEntity
    Dim oElSta() As String
   
   
    myXML.async = False
    myXML.validateOnParse = False
    myXML.Load ("W:\Bridge Inspection\Special Inspection - Drawings\UW - Drawings\Bridge_Scour.xml")
   
    Set myXElem = myXML.documentElement
    For Each myXRecord In myXElem.childNodes
        Debug.Print "========SOMETHING======"
        Debug.Print "BaseName " & myXElem.baseName  'Bridge_Scour
        Debug.Print "Structure Name " & myXRecord.Attributes(0).nodeValue
        For Each myXField In myXRecord.childNodes
            Debug.Print "Year " & myXField.Attributes(0).nodeValue
            'Debug.Print myXField.baseName & "---" & myXField.Text
            'Debug.Print "Stations and Elevations " & myXField.Text
'            Debug.Print myXField.childNodes(0).baseName 'Survey
'            Debug.Print myXField.childNodes(0).childNodes(0).firstChild.baseName 'Station Text
'            Debug.Print myXField.childNodes(0).childNodes(0).lastChild.baseName  'Elevation
            oElSta = Split(myXField.Text, " ")
            'Loop through the stations and elevations of the years
            For i = 0 To myXField.childNodes.Length
                Debug.Print myXField.childNodes(0).childNodes(0).firstChild.baseName & " " & myXField.childNodes(0).firstChild.childNodes(i).Text 'Station Te
                Debug.Print myXField.childNodes(0).childNodes(0).lastChild.baseName & " " & myXField.childNodes(0).firstChild.childNodes(1).Text 'Elevation
            Next
           
'            For i = 0 To UBound(oElSta)
'                Debug.Print oElSta(i)
'            Next
        Next
       
    Next
End Sub


I WANT TO LOOP THROUGH THE STATION AND ELEVATION EACH AT THEIR OWN LINE


<?xml version="1.0"?>
<!DOCTYPE Bridge_Scour SYSTEM "Bridge_Scour.dtd">
<Bridge_Scour>
     <Structure Name = "uw1.dgn">
          <Surveys year="2000">
                <Survey>
                    <Entry><Station>0.1</Station> <Elevation>123.45</Elevation></Entry>
                    <Entry><Station>0.2</Station> <Elevation>123.46</Elevation></Entry>
               </Survey>
          </Surveys>
          <Surveys year="2001">
                <Survey>
                    <Entry><Station>0.3</Station> <Elevation>123.47</Elevation></Entry>
                    <Entry><Station>0.4</Station> <Elevation>123.48</Elevation></Entry>
               </Survey>
          </Surveys>
     </Structure>
     <Structure Name = "uw2.dgn">
          <Surveys year="2001">
                <Survey>
                    <Entry><Station>0.5</Station> <Elevation>123.49</Elevation></Entry>
                    <Entry><Station>0.6</Station> <Elevation>123.50</Elevation></Entry>
               </Survey>
          </Surveys>
     </Structure>
</Bridge_Scour>
0
Comment
Question by:sherrick123
[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
  • 3
  • 3
10 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16942900
My, thats a tough bit of reading :o)

What output are you wanting ? Its probably easier, and cleaner, to use the SelectNodes and SelectSingleNode methods to retrieve the data you want rather than trying to traverse nested childNodes collections.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 16943401
Is space allowed between name an value

<Structure Name = "uw1.dgn">

try to

<Structure Name="uw1.dgn">

Appart from that the data looks ok.
0
 

Author Comment

by:sherrick123
ID: 16943445
I want the Station and Elevation data
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16943741
I mean't more along the lines of what data do you need from the overall document. You have a lot of Debug statements so I was wondering if you were just outputting them because you could, or if you actually need them all.
0
 

Author Comment

by:sherrick123
ID: 16944089
okay,  sorry about that.
What i am really needing is that for a particular year for a particular structure i want the station and elevation data.
its a one to many relationship.  for One Structure their will be Many Years and for Each Year their of corse be many Station and Elevations.

So in the end I want to populate a list box of Structures.
The user Pics a structure Then a list box will be populated with the Years
The User then Pics the years and Then a datagrid will be populated with the
Station and elevation.  

This is my first time with XML and I am just figuring the Nodes and ChildNodes and the navigation of the XML file

Thanks
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 16944301
Ok, gotcha.

Generally speaking, unless you are working with a very simple structure (no more than one or two levels deep) then using the childNodes collection is a bit cumbersome. Hopefully this sample for your problem will demonstrate a simpler mechanism:

    '// Assuming sName = "uw1.dgn" and sYear = "2001"

    Dim myXML As New MSXML2.DOMDocument
    Dim oSurvey As MSXML2.IXMLDomNodeList
    Dim oNode As MSXML2.IXMLDomNode
    Dim oElSta() As String
   
    myXML.async = False
    myXML.validateOnParse = False
    myXML.Load ("W:\Bridge Inspection\Special Inspection - Drawings\UW - Drawings\Bridge_Scour.xml")

    '// Grab all Entry nodes for the Year we are interested in
    Set oSurvey = myXML.SelectNodes("Bridge_Scour/Structure[@Name='" & sName & "']/Surveys[@year='" & sYear & "']/Survey/Entry")

    '// Check that we got something
    If Not oSurvey Is Nothing Then
        Debug.Print "Found " & oSurvey.Length & " matches"
        '// Loop through each node and retrieve Station and Elevation
        For Each oNode In oSurvey
            Debug.Print "Station: " & oNode.ChildNodes(0).Text
            Debug.Print "Elevation: " & oNode.ChildNodes(1).Text
        Next oNode
    End If
0
 

Author Comment

by:sherrick123
ID: 16944594
oh now that was alot easier than what i was trying to do.  I think i will be able to incorporate some of my code into yours and have everything work just fine.

Thanks so much
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

721 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