Excel VBA：如何删除/注入特定 XPath 下的所有节点？

Excel VBA: How to remove/inject all nodes under specific XPath?

提问by daZza

I am having trouble implementing a loop which should remove all nodes below a XPath. Once those nodes are deleted, another loop should inject nodes from a second document. So basically I want to replace old data within an already existing file with new data.

我在实现一个循环时遇到了问题，该循环应该删除 XPath 下的所有节点。一旦这些节点被删除，另一个循环应该从第二个文档中注入节点。所以基本上我想用新数据替换现有文件中的旧数据。

I think I got the basic framework covered, however, there still could be some problems with the XPath itself I guess. Also I couldn't get any method to remove childs working :(

我想我已经涵盖了基本框架，但是，我猜 XPath 本身仍然可能存在一些问题。我也找不到任何方法来删除孩子的工作:(

It would be much appreciated if someone could push me in the right direction as I basically have no prior knowledge regarding XML parsing using VBA.

如果有人能将我推向正确的方向，我将不胜感激，因为我基本上没有关于使用 VBA 解析 XML 的先验知识。

best regards, daZza

最好的问候，达扎

Here's my code so far:

到目前为止，这是我的代码：

VBA:

VBA：

Sub injectXML()

Dim sourceXML As DOMDocument60
Dim targetXML As DOMDocument60
Dim sourceNList As IXMLDOMNodeList 
Dim sourceNode As IXMLDOMNode
Dim targetNList As IXMLDOMNodeList
Dim targetNode As IXMLDOMNode
Dim sourceAttributes As IXMLDOMNamedNodeMap
Dim targetAttributes As IXMLDOMNamedNodeMap

Set sourceXML = New DOMDocument60
sourceXML.Load ("C:\...\source.xml")

Set targetXML = New DOMDocument60
targetXML.Load ("C:\...\target.xml")

sourceXML.async = False
sourceXML.validateOnParse = False
targetXML.async = False
targetXML.validateOnParse = False

Set sourceNList = sourceXML.SelectNodes("//test/TLRule")
Set targetNList = targetXML.SelectNodes("//RuleCollection/TLRule/TLRule")


For Each targetNode In targetNList

    [remove the node]   

Next targetNode


For Each sourceNode In sourceNList

    [append all Data from source.xml to target.xml]  

Next sourceNode


sourceXML.Save ("C:\...\target.XML")

End Sub

source XML structure:

源 XML 结构：

<test>
    <TLRule>
        <RuleCommand>
            <RuleID Value="0004" />
        </RuleCommand>
    </TLRule>
    <TLRule>
        <RuleCommand>
            <RuleID Value="0005" />
        </RuleCommand>
    </TLRule>
</test>

target XML structure:

目标 XML 结构：

<RuleCollection>
     <TLRule>

        [lots of other data in <TLRule> + child tags]

        <TLRule>
            <RuleCommand>
                <RuleID Value="0004" />
        <TLRule>
    <TLRule>
<RuleCollection>

Edit:

编辑：

Thanks to Martin I was able to get done the first part of my macro (deleting all the old data).

感谢 Martin，我能够完成宏的第一部分（删除所有旧数据）。

Set targetNList = targetXML.SelectNodes("//RuleCollection/TLRule/TLRule/RuleCommand")
Set targetNListSubstitution =    targetXML.SelectNodes("//RuleCollection/TLRule/TLRule/RuleSubstitution")
Set targetNListCleanUp = targetXML.SelectNodes("//RuleCollection/TLRule/TLRule")


For Each Item In targetNList
    Item.ParentNode.RemoveChild (Item)
Next


For Each Item In targetNListSubstitution
    Item.ParentNode.RemoveChild (Item)
Next


For Each Item In targetNListCleanUp
    If Item.HasChildNodes = False Then Item.ParentNode.RemoveChild (Item)
Next

Now I 'just' need to inject all the data from my sourceXML / sourceNList. This seems to be the hardest part of the task though, as it needs to be injected in the place where I deleted the old data using the above methods.

现在我“只”需要从我的 sourceXML/sourceNList 注入所有数据。不过，这似乎是任务中最难的部分，因为它需要注入我使用上述方法删除旧数据的地方。

From my point of view I'd need some kind of anchor/pointer for that, but I just can't see how I would get one in there.

从我的角度来看，我需要某种锚点/指针，但我不知道如何在那里找到一个。

Maybe some of you can help? Would be much appreciated.

也许你们中的一些人可以提供帮助？将不胜感激。

best regards, daZza

最好的问候，达扎

采纳答案by Martin Honnen

I think

我认为

Set targetNList = targetXML.SelectNodes("//RuleCollection/TLRule/TLRule")


For Each targetNode In targetNList

    [remove the node]   

Next targetNode

can be implemented as

可以实现为

Set targetNList = targetXML.SelectNodes("//RuleCollection/TLRule/TLRule")

For Each item In targetNList
  item.parentNode.removeChild item
Next

As your paths /RuleCollection/TLRule/TLRule/RuleCommandand //RuleCollection/TLRule/TLRule/RuleSubstitutionselect child nodes of //RuleCollection/TLRule/TLRuleit should suffice to run the For Eachloop on targetXML.SelectNodes("//RuleCollection/TLRule/TLRule").

由于您的路径/RuleCollection/TLRule/TLRule/RuleCommand和//RuleCollection/TLRule/TLRule/RuleSubstitution选择//RuleCollection/TLRule/TLRule它的子节点应该足以For Each在targetXML.SelectNodes("//RuleCollection/TLRule/TLRule").

As for inserting the nodes from the other document, I think you can use e.g.

至于从其他文档插入节点，我认为你可以使用例如

Set target = targetXML.selectSingleNode("//RuleCollection/TLRule")
For Each sourceNode In sourceNList
  target.appendChild sourceNode
Next