0

I have an XML program found here that takes a specific child node of one tag and place it as a child of another tag. Part of the code involves telling VBA how to traverse the tree, which makes perfect sense. The problem is that when I actually open up the tree structure in the 'Locals' window in the Visual Basic editor I find myself unable to access the part of the tree that seems clearly visible from the actual XML file. The XML I am working with is similar to the following block:

<Root> 
<Results xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <Reference>{REFERENCE-HERE}</Reference>
      <FillerTags>Filler</FillerTags>
      <entity>
        <entityName>ABC</entityName>
        <entityId>012345</entityId>
      </entity>
      <Items>
        <Item>
          <entityId>012345</entityId>
          <FillerTagsAgain1>Filler2</FillerTagsAgain1>
          <FillerTagsAgain2>Filler2</FillerTagsAgain2>
          <FillerTagsAgain3>Filler2</FillerTagsAgain3> 
         </Item>
         <Item> 
           <entityId>012345</entityId>
           <FillerTagsAgain1>Filler2</FillerTagsAgain1>
           <FillerTagsAgain2>Filler2</FillerTagsAgain2>
           <FillerTagsAgain3>Filler2</FillerTagsAgain3> 
         </AnotherItem>
       </Items>
       <Contents>
       <MoreFiller>asdf</MoreFiller>
       </Contents>
   </Results>
   <Results>
     <entity>
      <entityName>DEF</entityName>
        <entityId>678910</entityId>
      </entity>
      <Items>
        <Item>
          <entityId>678910</entityId>
          <FillerTagsAgain1>Filler2</FillerTagsAgain1>
          <FillerTagsAgain2>Filler2</FillerTagsAgain2>
          <FillerTagsAgain3>Filler2</FillerTagsAgain3> 
         </Item>
         <Item> 
           <entityId>678910</entityId>
           <FillerTagsAgain1>Filler2</FillerTagsAgain1>
           <FillerTagsAgain2>Filler2</FillerTagsAgain2>
           <FillerTagsAgain3>Filler2</FillerTagsAgain3> 
         </Item>
       </Items> 
      <Contents>
      <MoreFiller>asdf</MoreFiller>
     </Contents>  
   </Results>
</Root>

The inclusion of both Items and Contents is just to try to clarify that there are multiple blocks below the block. Here 'entity' is Customer, Item is Transaction, and Contents is Indicators.

For example, the node to be copied is a child of 'Customer'. Customer has several siblings, call them Sibling1 = Transactions = Items,Sibling2 = Indicators = Contents, and Sibling3 = Validation (not present in the example XML). When I open up the locals window I start at the child node of Customer. I would like to access the children of Sibling1. To do this I use the route Customer.ParentNode.NextSibling.ChildNodes and the code runs fine. However if I change this to Customer.ParentNode.NextSibling.NextSibling.ChildNodes VBA suggests that Sibling3 is in fact Sibling2. That is, following the path shows Validation and not Indicators. However, looking at the actual XML file it does seem clear that Indicators comes after Transactions. Is there any clear reason why this might be?

EDIT: Attached below is a picture of the tree structure I am referencing along with the code referencing it.

enter image description here

For Each Customer In DOM.DocumentElement.getElementsByTagName("CustomerId")
   'Since Indicators is Customer nextSibling, and Customer is parent of CustomerId,'
   ' we can iterate the collection if its childNodes like this:'
   For Each itm In Customer.ParentNode.NextSibling.ChildNodes
        If itm.HasChildNodes Then
            '# Insert this node before the first child node of Indicators'
            itm.InsertBefore Customer.CloneNode(True), itm.FirstChild
        Else
            '# Append this node to the Indicators'
            itm.appendChild Customer.CloneNode(True)
        End If
    Next
Next

where customer is an XML node and as Transactions is its first sibling. In reference to the example XML Customer is entity and Transactions is Items.

Suppose I instead want to reach the second sibling. I would expect that the path would instead be (considering the example code above) `Customer.ParentNode.NextSibling.NextSibling.ChildNodes. However, if I try this I receive an "object not found" error and the tree looks like this:

enter image description here

NOTE: In both of the images above I have removed information from the middle column just so that the values for some nodes remain confidential.

The core problem is that 'Validation' is not actually the next sibling of Transactions. Indicators is the next sibling of transactions, and takes the role of <Contents> in the example XML.

Again in the interest of making this post self-contained, the code I am using is as follows:

Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub ParseResults()
'Most up-to-date copy of XML Parsing Macro
'Requires reference to Microsoft XML, v6.0
'Requires referenc to Microsoft Scripting Runtime
Dim xmlFilePath$, newFilePath$
Dim DOM As MSXML2.DOMDocument
Dim Customer As IXMLDOMNode
Dim fso As Scripting.FileSystemObject
Dim itm As IXMLDOMNode

'# Define the file you are going to load as XML
xmlFilePath = "C:\FAKEPATH\FAKEFILE.xml"

'# Define an output path for where to put the modified XML
newFilePath = "C:\FAKEPATH\FAKEFILE.xml"

'# Create our DOM object
Set DOM = CreateObject("MSXML2.DOMDocument.6.0")

'# Load the XML file
DOM.Load xmlFilePath

'# Wait until the Document has loaded
Do
    Sleep 250
Loop Until DOM.readyState = 4

'##### NO LONGER USED:'
'# Get the CustomerId node'
'Set Customer = DOM.DocumentElement.getElementsByTagName("CustomerId")(0)'

'# Instead of getting the first Transaction like we did before, we can iterate the collection'
' of nodes with the CustomerId tag like so:'
For Each Customer In DOM.DocumentElement.getElementsByTagName("CustomerId")
   'Since Transaction is Customer nextSibling, and Customer is parent of CustomerId,'
   ' we can iterate the collection if its childNodes like this:'
   For Each itm In Customer.ParentNode.NextSibling.ChildNodes
        If itm.HasChildNodes Then
            '# Insert this node before the first child node of Transaction'
            itm.InsertBefore Customer.CloneNode(True), itm.FirstChild
        Else
            itm.appendChild Customer.CloneNode(True)
            '# Append this node to the Transaction'
        End If
    Next
Next


'##### This function call is no longer needed
'AppendCustomer DOM, "Transaction", Customer'

'##### This function call is no longer needed
'AppendCustomer DOM, "Transaction", Customer'

'## Create an FSO to write the new file
Set fso = CreateObject("Scripting.FileSystemObject")

'## Attempt to write the new/modified XML to file
On Error Resume Next
'MsgBox DOM.XML
fso.CreateTextFile(newFilePath, True, False).Write DOM.XML
If Err Then
    '## Print the new XML in the Immediate window
    Debug.Print DOM.XML
    MsgBox "Unable to write to " & newFilePath & " please review XML in the Immediate window in VBE.", vbInformation
    Err.Clear
End If
On Error GoTo 0
'Cleanup
Set DOM = Nothing
Set fso = Nothing
Set Customer = Nothing

End Sub

Sub AppendCustomer(DOM As Object, Transaction As String, copyNode As Object)
'## This subroutine will append child node to ALL XML Nodes matching specific string tag.
Dim TransactionColl As IXMLDOMNodeList
Dim itm As IXMLDOMNode

'# Get a collection of all elements matching the tagName
Set TransactionColl = DOM.DocumentElement.getElementsByTagName(Transaction)

'# Iterate over the collection, appending the copied node
For Each itm In TransactionColl
    If itm.HasChildNodes Then
        '# Insert this node before the first child node of Transaction
        itm.InsertBefore copyNode.CloneNode(True), itm.FirstChild
    Else
        '# Append this node to the Transaction
        itm.appendChild copyNode.CloneNode(True)
    End If
Next

Set itm = Nothing
Set TransactionColl = Nothing

End Sub
Community
  • 1
  • 1
114
  • 876
  • 3
  • 25
  • 51
  • You make it almost impossible to help by talking about a tree structure that has nothing in common with the tree structure you posted, and then asking readers to translate. Post a sample of the actual structure you describe as broken, and describe specifically where that sample renders a bad result in your code. – downwitch Jun 18 '14 at 03:58
  • @downwitch My issue was definitely that I was trying to combine two problems. I have decided to focus on one of them. The error here is that the XML 'skips' a sibling and goes to the next sibling as though the middle sibling does not exist. It would be as if it skipped the `` node entirely. – 114 Jun 18 '14 at 19:19

1 Answers1

1

Assuming your xml looks something like this (xml namespace and stuff omitted for clarity):

<root>
    <Customer>
        <child1>asdf</child1>
        <child2>sdfg</child2>
        ...
    </Customer>
    <Sibling1>
        ...
    </Sibling1>
    <Sibling2>
        ...
    </Sibling2>
    <Sibling3>
        ...
    </Sibling3>
</root>

Then Customer.NextSibling would be Sibling1, Customer.NextSibling.NextSibling would be Sibling2 and so on.

The only reason to use ParentNode to get to Sibling1 is if your current node is child1 of Customer. Then you could refer to the children of Sibling1 with child1.ParentNode.NextSibling.ChildNodes.

Blackhawk
  • 5,984
  • 4
  • 27
  • 56
  • You are exactly right with your last statement. The issue I seem to be having is that despite following that reasoning the path I specify does not work. I will post the code I am using for clarity. – 114 Jun 16 '14 at 17:08
  • Which node is the `Customer` variable actually pointing to when you perform your "after" check? I recommend going through the full DOM to validate the modified nodes rather than trying to reuse a variable that was just used for iteration which might be pointing to something different than you expect. – Blackhawk Jun 16 '14 at 17:46
  • Customer actually points to the Customer node on the initial run through. – 114 Jun 16 '14 at 17:58
  • Actually, judging by the code, Customer points to a "CustomerId" node, which is a child of "Customer". Each iteration through the inner loop, it would point to a different instance of a "CustomerId" node. In my example, this would be `child1`. `child1.ParentNode.NextSibling.ChildNodes` would get you the child nodes of `Sibling1` and `child1.ParentNodes.NextSibling.NextSibling.ChildNodes` would get you the children of `Sibling2`. – Blackhawk Jun 16 '14 at 18:15
  • You're right, and this is exactly where I ran into the "object not set" issue initially. Apologies for the lack of clarity. Despite using Customer.ParentNode.NextSibling.NextSibling.ChildNodes I get an error. When I go through the tree using the exact same file that worked when only using .NextSibling something strange happens - the tree is changed so that the sibling **after** (which in the real file is called ) is said to be the next sibling. Again, looking at the file though this is clearly not the case. – 114 Jun 16 '14 at 18:47
  • I'm sorry, but it's still not clear to me what you are talking about - can you perhaps supply an example of your actual xml structure with the data removed? – Blackhawk Jun 16 '14 at 19:19
  • Take child1 for example. Do you mean that `child1.ParentNode.NextSibling` is Sibling1, but `child1.ParentNode.NextSibling.NextSibling` is giving you Sibling3 instead of Sibling2? – Blackhawk Jun 16 '14 at 19:26
  • Sorry about that, that's what I meant - Sibling3 is being shown instead of Sibling2 for some reason. – 114 Jun 16 '14 at 20:06
  • Try the equivalent of `child1.ParentNode.ParentNode.ChildNodes` and inspect them in the watch window. That should reveal any issues with the order. – Blackhawk Jun 16 '14 at 20:41
  • Do you mean trying the equivalent of that and inspecting in the locals window? When looking in that window the path I see to the node I want to target is the one we previously mentioned. – 114 Jun 16 '14 at 20:53
  • I have tried a variety of different order combinations without any success. Would my error only be possible if I was making a mistake, or are there other possible explanations? – 114 Jun 18 '14 at 14:15