getElementsByTagName returning zero elements with DOMDOCUMENT60 in VBA

Viewed 519

I have a piece of VBA code that worked perfectly in an older environment, using MSXML2.DOMDOCUMENT objects. However, now that I have had to turn these into MSXML2.DOMDOCUMENT60 objects (because Excel 64-bit doesn't support MSXML2.DOMDOCUMENT) it no longer works.

Specifically, I am getting zero nodes returned for getElementsByTagName (although I am having similar issues with e.g. selectNodes or selectSingleNode. I suspect namespaces (and I have read innumerable different posts on the topic), but I cannot make it work.

Note that the data is all present in the file, and if I use the Locals window in the VBA editor then I can inspect it all. It just refuses to be returned by the code.

This is the code I am using:

' Open the TXC XML file
Dim TXCDoc As New msxml2.DOMDocument60
With TXCDoc
    .async = False
    .validateOnParse = True
    .setProperty "SelectionLanguage", "XPath"
    .setProperty "SelectionNamespaces", "xmlns=""http://www.transxchange.org.uk"""
    .Load filename
End With

If TXCDoc.parseError.ErrorCode <> 0 Then
    MsgBox "Parsing error in file " & filename & Chr$(10) & Chr$(10) & TXCDoc.parseError.reason & Chr$(10) & Chr$(10) & _
           "Line: " & TXCDoc.parseError.Line & ":" & TXCDoc.parseError.linepos, vbCritical + vbOKOnly, "TXC Validation Error"
End If
    
' Read the VehicleJourneys    
Dim xmlVjList As IXMLDOMNodeList
Dim xmlVj As IXMLDOMNode
Set xmlVjList = TXCDoc.getElementsByTagName("VehicleJourney")
For Each xmlVj In xmlVjList
    ' Do stuff
Next xmlVj

[EDIT to clarify - xmVjList returns zero VehicleJourney nodes]

and this is a snippet of the XML file that I am trying to load:

<?xml version="1.0" encoding="utf-8"?>
<TransXChange xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:schemaLocation="http://www.transxchange.org.uk/ http://www.transxchange.org.uk/schema/2.1/TransXChange_general.xsd" CreationDateTime="2020-07-21T09:57:00-00:00" ModificationDateTime="2020-07-21T09:57:00-00:00" Modification="new" RevisionNumber="0" FileName="SVRYEAG001.xml" SchemaVersion="2.1" RegistrationDocument="false" xmlns="http://www.transxchange.org.uk/">
  <StopPoints> ... </StopPoints>
  <RouteSections> ... </RouteSections>
  <Routes> ... </Routes>
  <JourneyPatternSections> ... </JourneyPatternSections>
  <Operators> ... </Operators>
  <Services> ... </Services>
  <VehicleJourneys>
    <VehicleJourney> ... </VehicleJourney>
    <VehicleJourney> ... </VehicleJourney>
    <VehicleJourney> ... </VehicleJourney>
    <VehicleJourney> ... </VehicleJourney>
    <!-- there are 22 VehicleJourney nodes in total -->
  </VehicleJourneys>
</TransXChange>

So what am I doing wrong? Thanks.

1 Answers

Thank you to @gserg, this is the code that I am now using:

' Open the TXC XML file
Dim TXCDoc As New msxml2.DOMDocument60
With TXCDoc
    .async = False
    .validateOnParse = True
    .setProperty "SelectionLanguage", "XPath"
    .setProperty "SelectionNamespaces", "xmlns:txc=""http://www.transxchange.org.uk/"""
    .Load filename
End With

If TXCDoc.parseError.ErrorCode <> 0 Then
    MsgBox "Parsing error in file " & filename & Chr$(10) & Chr$(10) & TXCDoc.parseError.reason & Chr$(10) & Chr$(10) & _
       "Line: " & TXCDoc.parseError.Line & ":" & TXCDoc.parseError.linepos, vbCritical + vbOKOnly, "TXC Validation Error"
End If
    
' Read the VehicleJourneys    
Dim xmlVjList As IXMLDOMNodeList
Dim xmlVj As IXMLDOMNode
Set xmlVjList = TXCDoc.selectNodes("//txc:VehicleJourneys/txc:VehicleJourney")
For Each xmlVj In xmlVjList
    ' Do stuff
Next xmlVj
Related