CODEWITHSUNDEEP
xmlvbaexcel
Peter Panne
Peter Panne

Reputation: 485

XML parsing in Excel VBA doesn't find my node

I have an XML file to be parsed. That file contains information about a scuba dive. It is pretty simple structured:

<Dive xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Suunto.Diving.Dal">
   <Algorithm>1</Algorithm>
   <AltitudeMode>0</AltitudeMode>
   <AscentTime i:nil="true"/>
   <AvgDepth>9.36</AvgDepth>
   <Boat i:nil="true"/>
   <BottomTemperature>23</BottomTemperature>
   <BottomTime i:nil="true"/>
   <CnsEnd>1</CnsEnd>
   <CnsStart>0</CnsStart>
   <CylinderVolume>12</CylinderVolume>
   <CylinderWorkPressure>232000</CylinderWorkPressure>
   <Deleted i:nil="true"/>
   <DeltaPressure i:nil="true"/>
   <DesaturationTime i:nil="true"/>
   <DiveMixtures>
      <DiveMixture>
         <DiveGasChanges/>
         <EndPressure>47300</EndPressure> 
         <Helium>0</Helium>
         <Name i:nil="true"/>
         <Oxygen>21</Oxygen>
         <PO2>0</PO2>
         <Size>0</Size>
         <StartPressure>208100</StartPressure>
         <TransmitterId i:nil="true"/>
         <Type>4</Type>
   </DiveMixture>
</DiveMixtures>
<DiveNumberInSerie>2</DiveNumberInSerie>
<DiveSamples>
   <Dive.Sample>
      <AveragedTemperature>26</AveragedTemperature>
      <Ceiling i:nil="true"/>
      <Depth>1.23</Depth>
      <GasTime i:nil="true"/>
      <Heading i:nil="true"/>
      <Pressure>208100</Pressure>
      <SacRate>27.0936737</SacRate>
      <Temperature>26</Temperature>
      <Time>0</Time>
   </Dive.Sample>
   <Dive.Sample>
      <AveragedTemperature>26</AveragedTemperature>
      <Ceiling i:nil="true"/>
      <Depth>3.29</Depth>
      <GasTime i:nil="true"/>
      <Heading i:nil="true"/>
      <Pressure>206800</Pressure>
      <SacRate>28.29174</SacRate>
      <Temperature>26</Temperature>
      <Time>20</Time>
   </Dive.Sample>
</DiveSamples>
<DiveTags/>
<DiveTime i:nil="true"/>
<DivingDaysInRow i:nil="true"/>
<Duration>3595</Duration>
<EndPressure>47300</EndPressure>
<EndTemperature>25</EndTemperature>
</Dive>

Now I want to extract information like duration, AvgDepth etc. Furthermore I need each "Dive.Sample" container as a row in a excel sheet. Let's say I want to put the duration and AvgDepth in A1 and B1 of Sheet1 and all "Dive.Sample" in Sheet2 whereas each row represents a "Dive.Sample" container. My VBA code right now looks like this (I am just trying to get AvgDepth):

Dim xmlDoc As New MSXML2.DOMDocument
Dim xmlKnoten As IXMLDOMNode

Dim xpathKnoten As String
Dim xpathAttrib As String

xmlDoc.async = False
xmlDoc.validateOnParse = True                    ' Auf Fehler prüfen

If xmlDoc.Load(XmlDateiMitPfad) = False Then
   MsgBox "XML-Datei: '" & XmlDateiMitPfad & "' wurde nicht gefunden"
   Exit Sub
ElseIf xmlDoc.parseError = True Then
   MsgBox "XML-Datei: '" & XmlDateiMitPfad & "' hat fehlerhaften Aufbau (ist nicht 'wohlgeformt')"
   Exit Sub
End If

xmlDoc.setProperty "SelectionLanguage", "XPath"  

xpathKnoten = "/Dive/AvgDepth"                           
Set xmlKnoten = xmlDoc.SelectSingleNode(xpathKnoten)   

If xmlKnoten Is Nothing Then
     MsgBox "Knoten nicht gefunden. Vermutlich falsche XML-Struktur"
     Exit Sub
End If

With Tabelle1
   .Range("A3") = xmlKnoten.SelectSingleNode("AvgDepth").Text
End With

The issue is that the parser doesn't find the node /dive/AvgDepth. xmlKnoten is always nothing. What am I doing wrong?

How can I get deeper in the xml-structure to get my "Dive.Sample"?

Upvotes: 0

Views: 240

Answers (1)

Axel Richter
Axel Richter

Reputation: 61870

The problem are the multiple namespaces and the not explicitly prefixed default namespace. So XPath don't know which namespace should be used for "/Dive".

Either you use

...
xpathKnoten = "*[local-name(.) = 'Dive']"
Set xmlKnoten = xmlDoc.SelectSingleNode(xpathKnoten)

If xmlKnoten Is Nothing Then
     MsgBox "Knoten nicht gefunden. Vermutlich falsche XML-Struktur"
     Exit Sub
End If

With Tabelle1
   .Range("A3") = xmlKnoten.SelectSingleNode("*[local-name(.) = 'AvgDepth']").Text
End With
...

or you map the default namespace with a prefix for XPath:

...
xmlDoc.setProperty "SelectionLanguage", "XPath"

xmlDoc.setProperty "SelectionNamespaces", "xmlns:sdd=""http://schemas.datacontract.org/2004/07/Suunto.Diving.Dal"""

xpathKnoten = "/sdd:Dive"
Set xmlKnoten = xmlDoc.SelectSingleNode(xpathKnoten)

If xmlKnoten Is Nothing Then
     MsgBox "Knoten nicht gefunden. Vermutlich falsche XML-Struktur"
     Exit Sub
End If

With Tabelle1
   .Range("A3") = xmlKnoten.SelectSingleNode("sdd:AvgDepth").Text
End With
...

For your second question: "How can I get deeper in the xml-structure to get my "Dive.Sample"?"

I would do something like that:

...
Dim xmlNodeList As IXMLDOMNodeList

Set xmlNodeList = xmlDoc.SelectNodes("/sdd:Dive/sdd:DiveSamples/sdd:Dive.Sample")

For Each xmlKnoten In xmlNodeList
 MsgBox xmlKnoten.XML
Next
...

Upvotes: 1

Related Questions