VBA DOM Find Specific Nodes With Attributes

Question

I have a large XML file that I'm trying to parse using DOM. What I'm trying to do is only pull information from a node if it's parent contains a specific "id" attribute.

For example, if I only want to pull the TITLE or AUTHOR for books containing "id = Adventure" - how would I do it using DOM?

<?xml version="1.0"?>
<catalog>
<book id="Adventure">
   <author>Gambardella, Matthew</author>
   <title>XML Developer's Guide</title>
   <price>44.95</price>
</book>
<book id="Adventure">
   <author>Ralls, Kim</author>
   <title>Midnight Rain</title>
   <price>5.95</price>
</book>
<book id="Adventure">
   <author>Boal, John</author>
   <title>Mist</title>
   <price>15.95</price>
</book>
<book id="Mystery">
   <author>Ralls, Kim</author>
   <title>Some Mystery Book</title>
   <price>9.95</price>
</book>
</catalog>

Now using this code:

Sub mySub()

Dim mainWorkBook As Workbook
Dim XCMFILE As Variant
Dim BookType As String
Dim BookTitle As Variant
Dim Title As String
Dim n As IXMLDOMNode
Dim i As Integer

Set mainWorkBook = ActiveWorkbook
Set XCMFILE = CreateObject("Microsoft.XMLDOM")

XCMFILE.Load (XCMFileName) 'Load XCM File


i = 0

For Each n In XCMFILE.SelectNodes("/catalog/book")
    BookType= n.Attributes.getNamedItem("id").Text 'Search for id attribute within node
    If BookID = "Adventure" Then
        Set BookTitle = = XCMFILE.SelectNodes("/catalog/book/title/text()")
        Title = BookTitle(i).NodeValue
        mainWorkBook.Sheets("Sheet1").Range("B" & i + 3).Value = BookTitle'Prints values into B column
        i = i + 1
    EndIf

Next
End Sub

I don't know how to increment properly to only pull the nodes that fit my specification/category.

Thanks for any tips and pointers - I appreciate it.

The XPath in XCMFILE.SelectNodes("/catalog/book") should be able to only select the id=Adventure nodes, so they would be the only nodes you need to iterate — Mathieu Guindon
– Mathieu Guindon, Commented Mar 16, 2015 at 16:17
I'm not quite sure what you mean by XPath. Are you saying that since Adventure is the first type that appears, it would only go to that? — NRH
– NRH, Commented Mar 16, 2015 at 16:30
The string you put there is called an XPath; there's a way to make it only return the nodes you're interested in, lookup "xpath select nodes with specific attribute value" ;) — Mathieu Guindon
– Mathieu Guindon, Commented Mar 16, 2015 at 16:32
Thanks a lot Mat. One quick question though, I keep getting an error "Compile Error: Expected: list separator or )" Any idea what this means? My line of code reads BookTitle.SelectNodes("/catalog/book[@id="bk102"/author/text()")... the error actually highlights "bk102" — NRH
– NRH, Commented Mar 16, 2015 at 18:41
You need a set of quotes to tell VBA "hey look, that's a literal string", and then since that string literal needs to contain quotes, you need to put 2 for each one so that VBA won't think "oh hey, so your string literal ends here right?" and go boom when it finds that the next character is also a double-quote ;) so """hello, world""" outputs "hello, world" — Mathieu Guindon
– Mathieu Guindon, Commented Mar 16, 2015 at 18:52

Mathieu Guindon · Accepted Answer · 2015-03-16 19:05:36Z

1

You don't need to increment anything. Just write an XPath query that selects only the nodes you're interested in:

For Each n In XCMFILE.SelectNodes("/catalog/book[@id=""Adventure""]")
    ' n is a book node with the "id" attribute value "Adventure"
Next

answered Mar 16, 2015 at 19:05

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NRH Over a year ago

Thanks buddy. I had to use single quotes for 'Adventure' without an error being thrown. Appreciate the clarification.

NRH Over a year ago

Is it possible to have multiple attribute conditions? For Each n In XCMFILE.SelectNodes("/catalog/book[@id=""Adventure""]" & @version="1.0")

NRH Over a year ago

Got it (I think), For Each n In XCMFILE.SelectNodes("/catalog/book[@id=""Adventure"" and @version=""& myVar &""]")

Collectives™ on Stack Overflow

VBA DOM Find Specific Nodes With Attributes

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related