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.