2

I have an XML file looking like this: 

<xc:XmlCache xmlns:xc="XmlCache" xmlns:mp="mx.MarketParameters" xmlns:rt="mx.MarketParameters.Rates" xmlns:rtcu="mx.MarketParameters.Rates.Curve">
    <xc:XmlCacheArea xc:value="MarketParameters">
        <mp:nickName xc:value="MDS" xmlns:mp="mx.MarketParameters">
            <mp:date xc:value="20160518">
                <rt:rate xmlns:rt="mx.MarketParameters.Rates">
                    <rtcu:curve xmlns:rtcu="mx.MarketParameters.Rates.Curve">
                        <rtcu:currency xc:value="AED">
                            <rtcu:label xc:value="AED FX">
                                <rtcu:type xc:value="Swap point">
                                    <rtcu:generator xc:value="USD/AED">
                                        <rtcu:market xc:value="">
                                            <rtcu:maturity xc:value="10M" xc:dates="20160523-20170323" xc:type="Fields">

I would like to get a list of nodes of type rtcu:maturity in VBA to perform some operations:

Dim xmlCurvesFileDOMDocument As New DOMDocument60
Dim listOfMaturities As IXMLDOMNodeList

xmlCurvesFileDOMDocument.Load (xmlCurvesFilePath)
Set listOfMaturities = xmlCurvesFileDOMDocument.SelectNodes("//xc:XmlCache/xc:XmlCacheArea/mp:nickName/mp:date/rt:rate/rtcu:curve/rtcu:currency/rtcu:label/rtcu:type/rtcu:generator/rtcu:market/rtcu:maturity")

(note: I'm omitting xmlCurvesFilePathbut it's correct, the file is loaded).

When I run this, on the set of listOfMaturities I get an error of type Reference to undeclared namespace prefix: 'xc'. If I remove xc from the path, it will tell me the problem is the other one (mp). If I remove all the namespaces from the XPath, it works but it loads nothing.

I've tried to search stack overflow and it seems that the solution would be to set the Selection Name spaces (like here and here).

However, when I try to do that: 

xmlCurvesFileDOMDocument.setProperty("SelectionNamespaces", "xmlns:xc='XmlCache'")

... I get a compile error telling me "Expected : =".

Does anyone have an idea on how I can declare my namespaces and parse this XML file?

Matteo NNZ
  • 11,930
  • 12
  • 52
  • 89
  • 4
    Don't use brackets on method calls in VBA: `xmlCurvesFileDOMDocument.setProperty "SelectionNamespaces", "xmlns:xc='XmlCache'"` – Fratyx Feb 23 '18 at 09:30
  • @Fratyx I realized that while StackOverflow was down for a while. I think you should write that as an answer, since it was the real solution! – Matteo NNZ Feb 23 '18 at 13:21
  • Problem was not only typographical, but user didn't assign the whole set of namespaces to get a XML nodelist :-) – T.M. Feb 23 '18 at 19:06

3 Answers3

2

Load XML file including namespaces

You didn't set the Namespaces before loading, try this code including an load error routine:

Option explicit             ' declaration head of code module

Sub LoadIt()
Dim xmlCurvesFilePath        As String
    xmlCurvesFilePath = ThisWorkbook.Path & "\xml\testxc.xml"  ' << change to your xml file

Dim xmlCurvesFileDOMDocument As New DOMDocument60
Dim listOfMaturities         As IXMLDOMNodeList
Dim XmlNamespaces            As String
   ' [*** define Namespaces ***] ' <<< NAMESPACE DEFINITION NEEDED :-)
    XmlNamespaces = "xmlns:xc='XmlCache' xmlns:mp='mx.MarketParameters' xmlns:rt='mx.MarketParameters.Rates' xmlns:rtcu='mx.MarketParameters.Rates.Curve'"

With xmlCurvesFileDOMDocument
    .setProperty "SelectionNamespaces", XmlNamespaces
    .setProperty "SelectionLanguage", "XPath"
    .resolveExternals = True
    .validateOnParse = True
    .async = False
    If .Load(xmlCurvesFilePath) Then   ' check correct loading
       Set listOfMaturities = .SelectNodes("//xc:XmlCache/xc:XmlCacheArea/mp:nickName/mp:date/rt:rate/rtcu:curve/rtcu:currency/rtcu:label/rtcu:type/rtcu:generator/rtcu:market/rtcu:maturity")
       Debug.Print listOfMaturities.Length
    Else
       Dim xPE        As Object    ' Set xPE = CreateObject("MSXML2.IXMLDOMParseError")
       Dim strErrText As String
       Set xPE = xmlCurvesFileDOMDocument.parseError
       With xPE
           strErrText = "Load error " & .ErrorCode & " xml file " & vbCrLf & _
          Replace(.URL, "file:///", "") & vbCrLf & vbCrLf & _
           xPE.reason & _
          "Source Text: " & .srcText & vbCrLf & vbCrLf & _
          "Line No.:    " & .Line & vbCrLf & _
          "Line Pos.: " & .linepos & vbCrLf & _
          "File Pos.:  " & .filepos & vbCrLf & vbCrLf
      End With
      MsgBox strErrText, vbExclamation
      Set xPE = Nothing
      Exit Sub
    End If
End With

' further code
' ...
' clear memory
Set xmlCurvesFileDOMDocument = Nothing
End Sub
Parfait
  • 104,375
  • 17
  • 94
  • 125
T.M.
  • 9,436
  • 3
  • 33
  • 57
  • 1
    Finally, it was just about removing the brackets from the setProperty command. Thanks for your answer anyway, +1! – Matteo NNZ Feb 23 '18 at 13:22
  • Glad it worked in correct syntax and declaring the complete set of namespaces. BTW avoid setting brackets in statements, you can run in issues; e.g. your XML loading in OP via `xmlCurvesFileDOMDocument.Load (xmlCurvesFilePath)` doesn't need the brackets, whereas the function variant as shown in my answer `If xmlCurvesFileDOMDocument.Load(xmlCurvesFilePath) Then` must have them in any case. – T.M. Feb 23 '18 at 17:40
1

Using Xml Linq

Imports System.Xml
Imports System.Xml.Linq
Module Module1
    Const FILENAME As String = "c:\temp\test.xml"
    Sub Main()
        Dim doc As XDocument = XDocument.Load(FILENAME)
        Dim namespaces = doc.Descendants().Select(Function(x) New With {.ns = IIf(x.ToString().Contains(":"), x.ToString().Substring(1, x.ToString().IndexOf(":")), String.Empty), .name = x.Name.LocalName}).ToArray()
    End Sub

End Module
jdweng
  • 33,250
  • 2
  • 15
  • 20
1

Finally, the mistake was just syntax. Instead of this: 

xmlCurvesFileDOMDocument.setProperty("SelectionNamespaces", "xmlns:xc='XmlCache'")

... I had to write it without brackets, declaring all the namespaces: 

xmlCurvesFileDOMDocument.setProperty "SelectionNamespaces", "xmlns:xc='XmlCache' xmlns:mp='mx.MarketParameters'" etc.
Matteo NNZ
  • 11,930
  • 12
  • 52
  • 89