sherrick123
asked on
Looping through XML Data
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).no deValue
For Each myXField In myXRecord.childNodes
Debug.Print "Year " & myXField.Attributes(0).nod eValue
'Debug.Print myXField.baseName & "---" & myXField.Text
'Debug.Print "Stations and Elevations " & myXField.Text
' Debug.Print myXField.childNodes(0).bas eName 'Survey
' Debug.Print myXField.childNodes(0).chi ldNodes(0) .firstChil d.baseName 'Station Text
' Debug.Print myXField.childNodes(0).chi ldNodes(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).chi ldNodes(0) .firstChil d.baseName & " " & myXField.childNodes(0).fir stChild.ch ildNodes(i ).Text 'Station Te
Debug.Print myXField.childNodes(0).chi ldNodes(0) .lastChild .baseName & " " & myXField.childNodes(0).fir stChild.ch ildNodes(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</Stati on> <Elevation>123.45</Elevati on></Entry >
<Entry><Station>0.2</Stati on> <Elevation>123.46</Elevati on></Entry >
</Survey>
</Surveys>
<Surveys year="2001">
<Survey>
<Entry><Station>0.3</Stati on> <Elevation>123.47</Elevati on></Entry >
<Entry><Station>0.4</Stati on> <Elevation>123.48</Elevati on></Entry >
</Survey>
</Surveys>
</Structure>
<Structure Name = "uw2.dgn">
<Surveys year="2001">
<Survey>
<Entry><Station>0.5</Stati on> <Elevation>123.49</Elevati on></Entry >
<Entry><Station>0.6</Stati on> <Elevation>123.50</Elevati on></Entry >
</Survey>
</Surveys>
</Structure>
</Bridge_Scour>
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).no
For Each myXField In myXRecord.childNodes
Debug.Print "Year " & myXField.Attributes(0).nod
'Debug.Print myXField.baseName & "---" & myXField.Text
'Debug.Print "Stations and Elevations " & myXField.Text
' Debug.Print myXField.childNodes(0).bas
' Debug.Print myXField.childNodes(0).chi
' Debug.Print myXField.childNodes(0).chi
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).chi
Debug.Print myXField.childNodes(0).chi
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</Stati
<Entry><Station>0.2</Stati
</Survey>
</Surveys>
<Surveys year="2001">
<Survey>
<Entry><Station>0.3</Stati
<Entry><Station>0.4</Stati
</Survey>
</Surveys>
</Structure>
<Structure Name = "uw2.dgn">
<Surveys year="2001">
<Survey>
<Entry><Station>0.5</Stati
<Entry><Station>0.6</Stati
</Survey>
</Surveys>
</Structure>
</Bridge_Scour>
Is space allowed between name an value
<Structure Name = "uw1.dgn">
try to
<Structure Name="uw1.dgn">
Appart from that the data looks ok.
<Structure Name = "uw1.dgn">
try to
<Structure Name="uw1.dgn">
Appart from that the data looks ok.
ASKER
I want the Station and Elevation data
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks so much
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.