Tuesday, 16 September 2014

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://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