Using Xpath in Excel to read a child node's attribute

Question

I am having problems getting the value of "Data Import" from the source_lvl2 child node. In Excel, I get a run-time error of 91, "Object variable or With block variable not set"

I can't see what I'm doing wrong - any advice?

VBA

Sub TestXML()
Dim XDoc As Object

Set XDoc = CreateObject("MSXML2.DOMDocument")
XDoc.async = False: XDoc.validateOnParse = False
XDoc.Load ("C:\171215-000438_1513346972.xml")

Set lists = XDoc.SelectNodes("/archive/primary_rnw_contact/source/source_lvl2")
Debug.Print lists(0).Attributes(0).Text
Set XDoc = Nothing
End Sub

XML

EXTRA CODE

<?xml version="1.1" encoding="UTF-8"?>
<archive product="RightNow" version="4.0" build="17.8.0.1.0.248" label="Archived Incident">

Well, that's annoying - I keep getting the '91 runtime error — pee2pee, Apr 02 '19 at 10:36
Unfortunately not as it contains confidential data - the screenshot above is as much as I can show unfortunately - happy to answer any other questions — pee2pee, Apr 02 '19 at 10:38
No worries, I understand. Copy this to a new notepad file `` and save it as `test.xml` Now try it with this xml file `Set lists = XDoc.SelectNodes("/archive/primary_rnw_contact/source/source_lvl1")` Does it work? if yes then there is something in the xml that we need to see... — Siddharth Rout, Apr 02 '19 at 10:40
It's the "" that is causing the issue - if I add it, it breaks, if I remove it, it works - any idea? — pee2pee, Apr 02 '19 at 10:47
Please use the snippet tool via [edit] to insert xml rather than images. Also, try running first with validateOnParse as True and check for errors. — QHarr, Apr 02 '19 at 11:08
Change `xml version="1.1"` to `xml version="1.0"` and it should work. — Siddharth Rout, Apr 02 '19 at 11:47
[Interesting read](https://stackoverflow.com/questions/16269460/which-version-of-msxml-began-to-support-parsing-xml-1-1) and [Interesting read](https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms760399(v=vs.85)) — Siddharth Rout, Apr 02 '19 at 12:30

score 0 · Answer 1 · answered Apr 02 '19 at 10:46

You could try the following (not tested):

Dim xmap As XmlMap
Dim k as Long
Dim oMyNewColumn As ListColumn
oMyList As ListObject

' Delete all previous XML maps
k = ThisWorkbook.XmlMaps.Count 'Detect all XML maps

For i = 1 To k
        ThisWorkbook.XmlMaps(i).Delete
Next i

...


Set xmap = ThisWorkbook.XmlMaps.Add("some_file.xml")

...

Set oMyNewColumn = oMyList.ListColumns.Add
    oMyList.ListColumns(3).XPath.SetValue xmap, "/archive/primary_rnw_contact/source/source_lvl2"

Using Xpath in Excel to read a child node's attribute

1 Answers1