XPath not working properly in Excel VBA with DOMDocument

We have XML data in the format below received from BACS Clearing:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<?xml-stylesheet href="file:///o:/Dev/Development Projects 2014/DP Team Utilities/D-02294 DDI Voucher XML Conversion Tool/DDIVoucherStylesheet.xsl" type="text/xsl" ?>
<VocaDocument xmlns="http://www.voca.com/schemas/messaging" xmlns:msg="http://www.voca.com/schemas/messaging" xmlns:cmn="http://www.voca.com/schemas/common" xmlns:iso="http://www.voca.com/schemas/common/iso" xmlns:env="http://www.voca.com/schemas/envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.voca.com/schemas/messaging http://www.voca.com/schemas/messaging/Voca_AUDDIS_AdviceofDDI_v1.0.xsd">
  <Data>
    <Document type="AdviceOfDDIReport" created="2014-08-19T00:59:15" schemaVersion="1.0">
      <StreamStart>
        <Stream>
          <AgencyBankParameter>234</AgencyBankParameter>
          <BankName>LLOYDS BANK PLC</BankName>
          <BankCode>9876</BankCode>
          <AgencyBankName>BANK OF CYPRUS UK LTD</AgencyBankName>
          <AgencyBankCode>5432</AgencyBankCode>
          <StreamCode>01</StreamCode>
          <VoucherSortCode>SC998877</VoucherSortCode>
          <VoucherAccountNumber>12348765</VoucherAccountNumber>
        </Stream>
      </StreamStart>
      <DDIVouchers>
        <Voucher>
          <TransactionCode> NEW</TransactionCode>
          <OriginatorIdentification><ServiceUserName>A SERVICE NAME </ServiceUserName><ServiceUserNumber>223344</ServiceUserNumber></OriginatorIdentification>
          <PayingBankAccount><BankName>A SMALL BANK UK LTD</BankName><AccountName>AN INDIVIDUAL  </AccountName><AccountNumber>77553311</AccountNumber><UkSortCode>SC776655</UkSortCode></PayingBankAccount>
          <ReferenceNumber>BACS001122       </ReferenceNumber>
          <ContactDetails><PhoneNumber>021 223344</PhoneNumber><FaxNumber> </FaxNumber><Address><cmn:AddresseeName>a name</cmn:AddresseeName><cmn:PostalName>a place</cmn:PostalName><cmn:AddressLine>an address</cmn:AddressLine><cmn:TownName>A Town</cmn:TownName><cmn:CountyIdentification> </cmn:CountyIdentification><cmn:CountryName>UNITED KINGDOM</cmn:CountryName><cmn:ZipCode>AA1 2BB</cmn:ZipCode></Address></ContactDetails>
          <ProcessingDate>2014-08-19</ProcessingDate>
          <BankAccount><FirstLastVoucherCode>FirstLast</FirstLastVoucherCode><AgencyBankCode>7890</AgencyBankCode><SortCode>SC223344</SortCode><AccountNumber>99886655</AccountNumber><TotalVouchers>1</TotalVouchers></BankAccount>
        </Voucher>
        <Voucher>
...

and when I load the xml into the XPathVisualizer tool it works fine with an XPath expression like this:

VocaDocument/Data/Document/DDIVouchers/Voucher

But when I use the same xpath in VBA in MS Excel to retrieve the values into a worksheet it is not working.

Here is the code I am using in MS Execl VBA:

    Dim nodeList As IXMLDOMNodeList
    Dim nodeRow As IXMLDOMNode
    Dim nodeCell As IXMLDOMNode

    Dim rowCount As Integer
    Dim cellCount As Integer
    Dim rowRange As Range
    Dim cellRange As Range
    Dim sheet As Worksheet
    Dim dom As DOMDocument60

    Dim xpathToExtractRow As String
    xpathToExtractRow = "VocaDocument/Data/Document/DDIVouchers/Voucher"

    ' OTHER XPath examples
    ' xpathToExtractRow = "VocaDocument/Data/Document/StreamStart/Stream/BankName"
    ' xpathToExtractRow = "VocaDocument/Data/Document/DDIVouchers/Voucher/ContactDetails/Address/cmn:AddresseeName" ' NOTICE cmn namespace!
    ' xpathToExtractRow = "VocaDocument/Data/Document/DDIVouchers/Voucher/ProcessingDate

    Set domIn = New DOMDocument60
    domIn.setProperty "SelectionLanguage", "XPath"

    domIn.load (Application.GetOpenFilename("XML Files (*.xml), *.xml", , "Please select the xml file"))
    Set sheet = ActiveSheet
    Set nodeList = domIn.DocumentElement.SelectNodes(xpathToExtractRow)
    Set nodeRow = domIn.DocumentElement.SelectSingleNode(xpathToExtractRow) '"/*/Data//StreamStart/Stream/*").nodeName

    rowCount = 0
    Workbooks.Add
    For Each nodeRow In nodeList
        rowCount = rowCount + 1
        cellCount = 0
        For Each nodeCell In nodeRow.ChildNodes
            cellCount = cellCount + 1
            Set cellRange = sheet.Cells(rowCount, cellCount)
            cellRange.Value = nodeCell.Text
        Next nodeCell
    Next nodeRow

    End Sub

so what am I missing, to I need to add namespaces to the DOM Object or something? And if so, whould I add all the namesspaces using xmlDoc.setProperty("SelectionNamespaces", ?

thanks

You need to register the default namespace :

xmlDoc.setProperty "SelectionNamespaces", "xmlns:ns='http://www.voca.com/schemas/messaging'"

Then you need to use the registered namespace prefix at the beginning of all nodes in the scope where default namespace declared :

ns:VocaDocument/ns:Data/ns:Document/ns:DDIVouchers/ns:Voucher

That's because descendant nodes inherit default namespace from ancestor automatically, unless a different default namespace declared at the descendant level, or a prefix that point to different namespace used.