excel - 从多个 ParentNode 中检索多个 ChildNode

Question

我在检索彼此下方的节点时遇到了一些麻烦。我不喜欢循环 for each node通过 parent ，因为真正的 XML 文件非常大，有很多子节点。我希望有另一种方法可以模拟我已经在做的事情。
我想打印食谱RECIPE_LABEL_STRING/text()使用内部子食谱 SUB_DATA/ID/text()问题是有多个具有不同子食谱的食谱，而我现在的做法是我将所有子食谱都附加到父食谱上。我需要“修复”第一个 elt里面SelecNodes .这可能吗？
我的代码:

Dim wb As Workbook
Set wb = ThisWorkbook
Dim oXMLFile As Object

        Set oXMLFile = CreateObject("Microsoft.XMLDOM")
        XMLFileName = ("C:\Users\x\Desktop\NodeSearch.xml")
        
        If XMLFileName = "" Then
            MsgBox "No XML found"
            Exit Sub
            Else
        End If

        If oXMLFile.Load(XMLFileName) Then
           'MsgBox "Loaded successfully"
        Else
          Exit Sub
        End If
        
        
        Set RecipeID = oXMLFile.SelectNodes("/ADEL:Definitions/RecipeCollection/RECIPE_NUMBER/RECIPE_DATA/elt/RECIPE_LABEL_STRING/text()")
        Set SubRecipeID = oXMLFile.SelectNodes("/ADEL:Definitions/RecipeCollection/RECIPE_NUMBER/RECIPE_DATA/elt/LAYER_DATA/elt/LAYER_SUB_DATA/SUB_DATA/ID/text()")
       
            Row = 2
                
            For i = 0 To RecipeID.Length - 1
                            
                wb.Sheets("Sheet1").Cells(Row, 1) = RecipeID(i).NodeValue
                
                    For s = 0 To SubRecipeID.Length - 1
                                
                    wb.Sheets("Sheet1").Cells(Row, 2) = 
SubRecipeID(s).NodeValue
                    Row = Row + 1
                                  
                    Next
                
                Row = Row + 1
                              
            Next

XML文件:

<?xml version="1.0" encoding="utf-8"?>
<!-- Automatically generated XML file -->
<ADEL:Definitions xmlns:ADEL="http://XMLSchema/MT/TWIN/ADEL/v6.2.1" 
xmlns:ADELrap="http://XMLSchema/MT/TWIN/ADEL/v6.2.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema- 
instance" xsi:schemaLocation="http://XMLSchema/MT/TWIN/ADEL/v6.2.1 ADEL.xsd">
 <RecipeCollection>
   <UnitRecipe></UnitRecipe>
    <RECIPE_NUMBER>
      <RECIPE_DATA>
        <elt>
          <RECIPE_LABEL_STRING>Recipe 1</RECIPE_LABEL_STRING>
          <LAYER_DATA>
            <elt>
              <LAYER_SUB_DATA>
                <SUB_DATA>
                  <ID>Sub Recipe 1</ID>
                </SUB_DATA>
              </LAYER_SUB_DATA>
            </elt>
             <elt>
              <LAYER_SUB_DATA>
                <SUB_DATA>
                  <ID>Sub Recipe 2</ID>
                </SUB_DATA>
              </LAYER_SUB_DATA>
            </elt>
          </LAYER_DATA>
        </elt>
        <elt>
          <RECIPE_LABEL_STRING>Recipe 2</RECIPE_LABEL_STRING>
          <LAYER_DATA>
            <elt>
              <LAYER_SUB_DATA>
                <SUB_DATA>
                  <ID>Sub Recipe 1</ID>
                </SUB_DATA>
              </LAYER_SUB_DATA>
            </elt>
            <elt>
              <LAYER_SUB_DATA>
                <SUB_DATA>
                  <ID>Sub Recipe 2</ID>
                </SUB_DATA>
              </LAYER_SUB_DATA>
            </elt>
            <elt>
              <LAYER_SUB_DATA>
                <SUB_DATA>
                  <ID>Sub Recipe 3</ID>
                </SUB_DATA>
              </LAYER_SUB_DATA>
            </elt>
          </LAYER_DATA>
        </elt>
        <elt>
          <RECIPE_LABEL_STRING>Recipe 3</RECIPE_LABEL_STRING>
           <LAYER_DATA>
            <elt>
              <LAYER_SUB_DATA>
                <SUB_DATA>
                  <ID>Sub Recipe 3</ID>
                </SUB_DATA>
              </LAYER_SUB_DATA>
            </elt>
          </LAYER_DATA>
        </elt>
      </RECIPE_DATA>
    </RECIPE_NUMBER>
  </RecipeCollection>
</ADEL:Definitions>

以及它现在如何打印的图片:

Answer 1

试试这个自上而下的解决方案

        Dim RecipeID As Object, SubRecipeID As Object
        Set RecipeID = oXMLFile.SelectNodes( _
            "/ADEL:Definitions/RecipeCollection/RECIPE_NUMBER/RECIPE_DATA/elt/RECIPE_LABEL_STRING")
       
       With Sheet1                      ' using a sheet's Code(Name)
            Dim i As Long, r As Long: r = 1
            For i = 0 To RecipeID.Length - 1
                r = r + 1
                .Cells(r, 1) = RecipeID(i).Text
                ' build XPath string starting from RecipeID(i) node's parent :-)
                Set SubRecipeID = RecipeID(i).ParentNode.SelectNodes("LAYER_DATA/elt/LAYER_SUB_DATA/SUB_DATA/ID")
                
                Dim ii As Long
                For ii = 0 To SubRecipeID.Length - 1
                    .Cells(r, 2) = SubRecipeID(ii).Text
                    r = r + 1
                Next ii
            Next
        End With