VBA DOM ParentNode SelectSingleNode

Question

<?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>

My program searches for all items by a specific author ("Ralls, Kim"), and at the same time gathers the BookType ("book id") and throws the values into an array.

The issue now though is that the book id's aren't always going to be the same. So since I've already established what the parent node is, I want to pull out the BookTitle - this way I don't have to put a variable in the XPATH that is already stored in an array (that sounds like a nightmare to me).

Sub mySub()

Dim XMLFile As Variant
Dim Author As Variant
Dim athr As String, BookType As String, Title As String
Dim AuthorArray() As String, BookTypeArray() As String, TitleArray() As  String
Dim i As Long, x As Long, j As Long

Dim mainWorkBook As Workbook
Dim n As IXMLDOMNode
Set mainWorkBook = ActiveWorkbook
Set XMLFile = CreateObject("Microsoft.XMLDOM")
XMLFile.Load ("C:\Books.xml")


x = 1
j = 0

Set Author = XMLFile.SelectNodes("/catalog/book/author/text()")
For i = 0 To (Author.Length - 1)
    ReDim Preserve AuthorArray(0 To i)
    ReDim Preserve BookTypeArray(0 To i)
    ReDim Preserve TitleArray(0 To i)
    athr = Author(i).NodeValue
    BookType = Author(i).ParentNode.ParentNode.getAttribute("id")
    Title = Author(i).ParentNode.SelectSingleNode("title")

    If athr = "Ralls, Kim" Then

        AuthorArray(j) = athr
        BookTypeArray(j) = BookType
        TitleArray(j) = Title

        j = j + 1
        x = x + 1
    End If
Next



Range("A3:A" & UBound(AuthorArray) + 1) = WorksheetFunction.Transpose(AuthorArray)
Range("B3:B" & UBound(BookTypeArray) + 1) = WorksheetFunction.Transpose(BookTypeArray)
Range("C3:C" & UBound(BookTypeArray) + 1) = WorksheetFunction.Transpose(TitleArray)


End Sub

I keep getting an object error on this line of code and I don't understand why:

Title = Author(i).ParentNode.SelectSingleNode("title")

Thanks for any help and suggestions.

Answer 1

Not familiar with VBA, but most likely you are getting an error because this line

Set Author = XMLFile.SelectNodes("/catalog/book/author/text()")

sets an Author variable (?) to a string value, text() means the concatenation of all text nodes that are immediate children of the context element. Strings are very different from elements, they do not have attributes, or child elements.

They do have a parent node, but

BookType = Author(i).ParentNode.getAttribute("id")

will try to retrieve the id attribute of the parent of such a text node, which is an author element - but author elements do not have an id attribute.

Change

Set Author = XMLFile.SelectNodes("/catalog/book/author/text()")

to

Set Author = XMLFile.SelectNodes("/catalog/book/author")

and let me know if that helps.

---

EDIT: I think I found a working solution, how about the following. However, I am absolutely unfamiliar with VBA - I'm sure this is not a very elegant solution and could be improved a lot.

Sub mySub()

Dim XMLFile As MSXML2.DOMDocument
Dim Author As Variant
Dim athr As String, BookType As String, Title As String
Dim AuthorArray() As String, BookTypeArray() As String, TitleArray() As String
Dim i As Long, x As Long, j As Long

Dim mainWorkBook As Workbook
Dim n As IXMLDOMNode
Set mainWorkBook = ActiveWorkbook
Set XMLFile = CreateObject("Microsoft.XMLDOM")
XMLFile.Load ("vba.xml")


x = 1
j = 0

Set Author = XMLFile.SelectNodes("/catalog/book/author")
For i = 0 To (Author.Length - 1)
    ReDim Preserve AuthorArray(0 To i)
    ReDim Preserve BookTypeArray(0 To i)
    ReDim Preserve TitleArray(0 To i)
    athr = Author(i).Text
    BookType = Author(i).ParentNode.getAttribute("id")
    Title = Author(i).ParentNode.getElementsByTagName("title").Item(0).nodeTypedValue

    If athr = "Ralls, Kim" Then

        AuthorArray(j) = athr
        BookTypeArray(j) = BookType
        TitleArray(j) = Title

        j = j + 1
        x = x + 1
    End If
Next



Range("A3:A" & UBound(AuthorArray) + 1) = WorksheetFunction.Transpose(AuthorArray)
Range("B3:B" & UBound(BookTypeArray) + 1) = WorksheetFunction.Transpose(BookTypeArray)
Range("C3:C" & UBound(BookTypeArray) + 1) = WorksheetFunction.Transpose(TitleArray)


End Sub

Result

Ralls, Kim  Adventure   Midnight Rain
Ralls, Kim  Mystery     Some Mystery Book

---

2nd EDIT

can you please explain what Item(0) is doing in this line Title = Author(i).ParentNode.getElementsByTagName("title").Item(0).nodeTypedValue?

getElementsByTagName() returns a collection of elements, even if there is one one result - it would end up in a collection with just one item. Item(0) selects the first element in this collection.

Then, nodeTypedValue returns the value of an object, depending on its data type. By the way, the NodeValue property only works on attributes - that's why your code returned an error.

Also, is there ever a need for using /text() within the XPath to the node (it doesn't seem to affect numbers or anything either)?

(Finally something I'm comfortable with! I think you would profit from an introduction or tutorial to XPath.)

text() is a highly overused construct, but yes, there are uses for it. Think about it that way: When XML is read into memory, a tree-like representation is constructed. In this representation, all kinds of objects are nodes; elements are nodes, attributes are nodes - and text content is a node.

Now, an XPath expression like

/catalog/book/author

selects an element node (or, possibly, a set of element nodes). In your case, it is an element node that has a text node as its child. Extending the path expression to

/catalog/book/author/text()

selects only the child text nodes of author elements. It's important to understand that an element and its text content are not the same.