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://ift.tt/W1Ln1j" xmlns:msg="http://ift.tt/W1Ln1j" xmlns:cmn="http://ift.tt/1r4NAFk" xmlns:iso="http://ift.tt/1w2xxIs" xmlns:env="http://ift.tt/1w2xxIx" xmlns:xsi="http://ift.tt/ra1lAU" xsi:schemaLocation="http://ift.tt/W1Ln1j http://ift.tt/1pDSPMY">
<Data>
<Document type="AdviceOfDDIReport" created="2014-08-19T00:59:15" schemaVersion="1.0">
<StreamStart>
<Stream>
<AgencyBankParameter>234</AgencyBankParameter>
<BankName>LLOYDS BANK PLC</BankName>
<BankCode>0004</BankCode>
<AgencyBankName>BANK OF CYPRUS UK LTD</AgencyBankName>
<AgencyBankCode>0234</AgencyBankCode>
<StreamCode>01</StreamCode>
<VoucherSortCode>SC300037</VoucherSortCode>
<VoucherAccountNumber>46990760</VoucherAccountNumber>
</Stream>
</StreamStart>
<DDIVouchers>
<Voucher>
<TransactionCode> NEW</TransactionCode>
<OriginatorIdentification><ServiceUserName>NUFFIELD HEALTH </ServiceUserName><ServiceUserNumber>417987</ServiceUserNumber></OriginatorIdentification>
<PayingBankAccount><BankName>BANK OF CYPRUS UK LTD</BankName><AccountName>NATALIA GREGORY </AccountName><AccountNumber>46990760</AccountNumber><UkSortCode>SC300037</UkSortCode></PayingBankAccount>
<ReferenceNumber>BACS0077595 </ReferenceNumber>
<ContactDetails><PhoneNumber>020 83395862</PhoneNumber><FaxNumber> </FaxNumber><Address><cmn:AddresseeName>RANALD LESLIE</cmn:AddresseeName><cmn:PostalName>NUFFIELD HEALTH </cmn:PostalName><cmn:AddressLine>NUFFIELD HOUSE</cmn:AddressLine><cmn:TownName>SURBITON</cmn:TownName><cmn:CountyIdentification> </cmn:CountyIdentification><cmn:CountryName>UNITED KINGDOM</cmn:CountryName><cmn:ZipCode>KT6 4BN</cmn:ZipCode></Address></ContactDetails>
<ProcessingDate>2014-08-19</ProcessingDate>
<BankAccount><FirstLastVoucherCode>FirstLast</FirstLastVoucherCode><AgencyBankCode>0234</AgencyBankCode><SortCode>SC300037</SortCode><AccountNumber>46990760</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
No comments:
Post a Comment