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
