read data element from XML file

I've got a Excel worksheet that needs to lookup data values in a XML file.
If the XML file is formated as such:

File_1
ParentNode1
ChildNode1
DataElement1
DataElement2
DataElement3
ChildNode2
DataElement1
DataElement2
ParentNode2
ChildNode1
DataElement1
DataElement2
DataElement3
ChildNode2
DataElement1
DataElement2

How would I lookup the data value in ParentNode2\ChildNode1\DataElement2? I
am using a reference to Microsoft XML v.4.0 in the code explorer for this
workbook.

Thanks In Advance.

Hi Steve,

I haven't played with this a year or so and no longer have the XML SDK
installed on this machine. I've dug out an old workbook with some code
in though.

There's a few ways. If you have the schema, you can address it directly
as an index I believe e.g.

xmlSch.documentElement.childNodes(2).childNodes(1).item(2).text

or if you know the name of the item:

Set root = xmlDoc.documentElement
Set oNodeList = root.childNodes

For Each Item In oNodeList
'do Artist
Set subItem = Item.selectSingleNode("./s:Artist")
myData(0, 0) = subItem.Attributes.getNamedItem("sortName").Text

'do Song info
myData(0, 1) = Item.Attributes.getNamedItem("title").Text
myData(0, 2) = Item.Attributes.getNamedItem("ID").Text

next item 'etc.

At least this is how I used to do it. I've pasted some code (with bits
edited out and amended) that might help you too. I've also code
somewhere that does a better, more generic job of loading data from
XMLs. I can have a look for it if you like.

HTH,
Gareth

Sub ImportXMLfile()

Dim xmlDoc As New MSXML2.DOMDocument
Dim root As IXMLDOMElement
Dim oNodeList As IXMLDOMNodeList
Dim Item As IXMLDOMNode
Dim wb As Workbook
Dim subItem As IXMLDOMNode
Dim myData(0, 0 To 10) As String
Dim iRow As Long
Dim j As Integer

xmlDoc.async = False
'Try and load our main file
If Not xmlDoc.Load(myPath & "file.xml") Then
MsgBox "Error loading"
Exit Sub
End If

' create a workbook
Set wb = Workbooks.Add

Set root = xmlDoc.documentElement
Set oNodeList = root.childNodes

iRow = 1

With wb.Sheets(1)

For Each Item In oNodeList

iRow = iRow + 1
On Error Resume Next
'do Artist and song
Set subItem = Item.selectSingleNode("./s:Artist")
myData(0, 0) = _
subItem.Attributes.getNamedItem("sortName").Text

myData(0, 1) = Item.Attributes.getNamedItem("title").Text
myData(0, 2) = Item.Attributes.getNamedItem("ID").Text
myData(0, 3) = Item.Attributes.getNamedItem("category").Text

'do Song info
Set subItem = Item.selectSingleNode("./s:Album")
myData(0, 8) = subItem.Attributes.getNamedItem("title").Text

'do Additional
Set subItem = Item.selectSingleNode("./s:SongExtra")
myData(0, 9) = subItem.Attributes.getNamedItem("label").Text

On Error GoTo 0

Range(.Cells(iRow, 1), .Cells(iRow, 11)) = myData

DoEvents
'blank out the array
For j = 0 To 10
myData(0, j) = ""
Next j


Next

End With

Set oNodeList = Nothing
Set subItem = Nothing
Set root = Nothing
Set wb = Nothing

End sub



Steve Moreno wrote:
> I've got a Excel worksheet that needs to lookup data values in a XML file.
> If the XML file is formated as such:
>
> File_1
> ParentNode1
> ChildNode1
> DataElement1
> DataElement2
> DataElement3
> ChildNode2
> DataElement1
> DataElement2
> ParentNode2
> ChildNode1
> DataElement1
> DataElement2
> DataElement3
> ChildNode2
> DataElement1
> DataElement2
>
> How would I lookup the data value in ParentNode2\ChildNode1\DataElement2? I
> am using a reference to Microsoft XML v.4.0 in the code explorer for this
> workbook.
>
> Thanks In Advance.
>