0

It's unclear if I should be using an alternative way to read CustomXmlParts.

The XML I have stored looks vaguelly like this:

<Kittens>
<Kitten>
  <Name>Mr. Meow</Name>
  <Age>2</Age>
</Kitten>
<Kitten>
  <Name>Claws McTwitcher</Name>
  <Age>3</Age>
</Kitten>
<Kittens>

The method I've used for pulling this out is to loop through like this:

For Each xmlPart In ThisWorkbook.CustomXMLParts
  If Not xmlPart.DocumentElement Is Nothing Then
    If xmlPart.SelectNodes("//Kittens").Count > 0 Then
      Set matchedXmlPart = xmlPart
      Exit For
    End If
  End If
Next

This gives me some kind of XML object.

Presently I've just added a Watch onto it to examine it. I would be expecting to loop through the children (the Kitten objects) and then put them into a dictionary or similar to use.

However I am puzzled in that I can look at the DocumentElement property which has ChildNodes with Count: 5 whereas I would expect 2. I am not sure what to look at next. If I look at Item 1 in the watch window, I would have expected to see something like ElementName: Kitten and ChildNodes: 2 but instead it is ChildNodes: 0.

Instead the first item appears as NextSibling which is puzzling.

I wonder if anyone could point me in the right direction? Should I be ditching this and using a different XML method? I can grab the raw XML easy enough so perhaps I should construct some kind of superior xml reader?

Edit: It seems like the 5 nodes are some weird behaviour, as some of them have BaseName: #text which is odd. If I ignore those the others look correct.

So I can probably solve this by just ignoring those with a BaseName of #text but I can see these appearing all over the place. Maybe it's due to vbCrLfs between the tags? Not sure.

NibblyPig
  • 51,118
  • 72
  • 200
  • 356
  • 1
    ChildNodes includes both element nodes and text nodes (ie. element text content). It's not just a collection of elements. – Tim Williams Jul 13 '21 at 15:35
  • @NibblyPig Maybe helpful: [Obtain .. names from xml](https://stackoverflow.com/questions/51887820/obtain-attribute-names-from-xml-using-vba/51919182#51919182) – T.M. Jul 13 '21 at 18:28
  • 1
    @TimWilliams It was the vbCrLf, basically `\r\n...` it was picking up the linebreak as an element. I replaced the \r\n with empty string and it has fixed the extra nodes issue and I think I can figure out the rest now that it makes sense. Thanks. – NibblyPig Jul 13 '21 at 19:34

0 Answers0