解析XML DOM在VBA Excel -父母和孩子

Question

我想从这个xml文件中提取所有曲目的"serialnum"列表，以及每个serialnum的父光盘的"category"。例如

00001   Type A
00002   Type A
...
00007   Type B
00008   Type B

我弄清楚了如何提取序列号列表，但是我如何从XML链的"上"几个级别提取相关的类别?我可以轻松地调整数组的大小，但为了简单起见，我将其长度保留为100。我对多维数组也很熟悉，所以也许我可以写一个二维数组，其中"Serialnum"在第一列，"Category"在第二列?我可以轻松地编写一个循环(或循环中的循环)来迭代该场景，但是我如何调用来获取每个Serialnum的"category"值呢?谢谢你。

XML:

<?xml version="1.0"?>
<compactdiscs>
  <compactdisc category="Type A">
    <artist type="individual">Frank Sinatra</artist>
    <title numberoftracks="4">In The Wee Small Hours</title>
   <tracks>
      <track serialnum="00001">In The Wee Small Hours</track>
      <track serialnum="00002">Mood Indigo</track>
      <track serialnum="00003">Glad To Be Unhappy</track>
      <track serialnum="00004">I Get Along Without You Very Well</track>
   </tracks>
    <price>$12.99</price>
  </compactdisc>
  <compactdisc category="Type B">
    <artist type="band">The Offspring</artist>
    <title numberoftracks="5">Americana</title>
   <tracks>
      <track serialnum="00005">Welcome</track>
      <track serialnum="00006">Have You Ever</track>
      <track serialnum="00007">Staring At The Sun</track>
      <track serialnum="00008">Pretty Fly (For A White Guy)</track>
   </tracks>
    <price>$12.99</price>
  </compactdisc>
</compactdiscs>

VBA (Excel)。这段代码只是提取Serialnum的列表，没有"类别"数据，但我想添加"类别"数据。

Sub CDload()
'Create new sheet for ouput
'Set x = ThisWorkbook.Worksheets.Add
'define the object and load the first XML sheet
Set Target_XML_File = CreateObject("Microsoft.XMLDOM")
Target_XML_File.Load (ActiveWorkbook.Path & "CDs.xml")
Set CurrentNode = Target_XML_File.SelectNodes("/compactdiscs/compactdisc/tracks/track/@serialnum")
Dim arrayx() As String
ReDim arrayx(100, 0)
For i = 0 To (CurrentNode.Length - 1)
    arrayx(i, 0) = CurrentNode(i).NodeValue
Next i
'write array to worksheet, starting on row 2
ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(101, 1)) = arrayx
End Sub

Answer 1

我回答了自己的问题。解决方案是将. parentnode语法与. getattribute("XXXXXX")语法结合使用。请注意，我还更改了"选择节点"语法，以便首先选择元素，然后使用"getattribute("XXXXXX")语法，而不是选择属性。参见下面的工作代码。

Sub CDload()
'define the object and load the first XML sheet
Set Target_XML_File = CreateObject("Microsoft.XMLDOM")
Target_XML_File.async = False
Target_XML_File.Load (ActiveWorkbook.Path & "CDs.xml")
Set CurrentNode = Target_XML_File.SelectNodes("/compactdiscs/compactdisc/tracks/track")
Dim arrayx() As String
ReDim arrayx(100, 0)
Dim arrayx2() As String
ReDim arrayx2(100, 0)
For i = 0 To (CurrentNode.Length - 1)
    arrayx(i, 0) = CurrentNode(i).getAttribute("serialnum")
    arrayx2(i, 0) = CurrentNode(i).ParentNode.ParentNode.getAttribute("category")
Next i

'write array to worksheet, starting on row 2
ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(101, 1)) = arrayx
ActiveSheet.Range(ActiveSheet.Cells(1, 2), ActiveSheet.Cells(101, 2)) = arrayx2
End Sub