read xml into variables

[impulse24]

Hi,

I'm trying to read from xml files, and put values into variables to load to a database. I have been trying to work with the XML Dom, but have not had much luck.

I have the following xml:

<readin>
<doc>
<object id="1"/>
<object id="2"/>
<object id="3"/>
<object id="11"/>
<object id="12"/>
<object id="13"/>
<toperson>
<name/>
<attention>Bart Simpson</attention>
<city/>
</toperson>
</doc>
<doc>
<dres>
<object id="15"/>
</dres>
</doc>
</readin>

Basically I want to get the text value for each of the different elements. The <doc> is the main node

So I want to grab each object id from within the <doc> node, as well as the name, attention, etc.

Below is the code I have so far:
Dim objxml As MSXML2.DOMDocument60
Set objxml = New MSXML2.DOMDocument60

objxml.Load (strxmlname)
Dim oelem As IXMLDOMElement
Dim onode As IXMLDOMNode
Dim olist As IXMLDOMNodeList


Set objElem = objxml.documentElement
Set oList = objxml.selectNodes("doc")
For Each oelem In oList
look = oelem.getAttribute("object id")
Next oelem

When running this it the for each loop is completely as if there is no list under the doc node. Any help would be greatly appreciated.

 

[tsuji]

This is the minimal re-work for illustration. Odd looking at selectnodes are due to the object nodes being under doc at varying depth!

>Set oList = objxml.selectNodes("doc")
[tt]Set oList = objxml.selectNodes("//doc//object")[/tt]

>look = oelem.getAttribute("object id")
[tt]look = oelem.getAttribute("id")[/tt]

 

[impulse24]

thanks tsuji, it's pretty close. With the xml above though i get a return of 7 object id's, which is basically all the object id's. What I need to do is loop through each doc element and get the object id's associated with that doc element, as well as the name/attention/city.

So from the xml above, i want to generate the following strings:

objectid|name|attention|city
1||Bart Simpson|
2||Bart Simpson|
3||Bart Simpson|
11||Bart Simpson|
12||Bart Simpson|
13||Bart Simpson|
15||||

Basically i am wanting to group information that is in the same doc node. Not sure how to get to that. Any help would be appreciated.

 

[tsuji]

I am not interested in chasing moving target. Close? good enough.
[tt]
For Each oelem In oList
look = oelem.getAttribute("object id")
If Not oelem.selectSingleNode("../toperson/attention") Is Nothing Then
attention = oelem.selectSingleNode("../toperson/attention").Text
Else
attention = ""
End If
Next oelem
[/tt]
The rest? all yours.

 

[tsuji]

amendment
Upon re-read what I posted, I had copied and pasted the block without putting in my correction to that getAttribute line.
[tt]
[blue]'oelem is per my version with "//doc//object"[/blue]
For Each oelem In oList
look = oelem.getAttribute([red]"id"[/red])
If Not oelem.selectSingleNode("../toperson/attention") Is Nothing Then
attention = oelem.selectSingleNode("../toperson/attention").Text
Else
attention = ""
End If
Next oelem
[/tt]