如何使用VBA从XML读取所有属性？

I got from a Webservice an XML. I declared it as a "DOMDocument". This is my XML. Now I want to read all Attributes named "ZIP".

我从Webservice获得了一个XML。我将其声明为“DOMDocument”。这是我的XML。现在我想读取所有名为“ZIP”的属性。

<?xml version="1.0" encoding="utf-8" ?> 
<Location>
  <Cities>
   <City ZIP="8355">Aadorf</City> 
   <City ZIP="5000">Aarau</City> 
   <City ZIP="5004">Aarau</City> 
   <City ZIP="5032">Aarau Rohr</City> 
   <City ZIP="3270">Aarberg</City> 
   <City ZIP="4663">Aarburg</City> 
   <City ZIP="4912">Aarwangen</City> 
   <City ZIP="8607">Aathal-Seegräben</City> 
   <City ZIP="8522">Aawangen</City> 
   <City ZIP="1657">Abländschen</City> 
   <City ZIP="5646">Abtwil AG</City> 
   <City ZIP="9030">Abtwil SG</City> 
  </Cities>
<Location>

With...

随着...

Private Sub Workbook_Open()

    Dim i As Integer
    Dim NumberOfElements As Integer
    Dim City As String
    Dim xmlUrl As String
    Dim xmlDoc As New DOMDocument

    xmlUrl = "http://localhost:62231/dataHandling.asmx/GetAllCities"
    xmlDoc.async = False

    If xmlDoc.Load(xmlUrl) = False Then
        MsgBox ("XML LOAD ERROR")
    Else

        NumberOfElements = xmlDoc.getElementsByTagName("City").Length

        For i = 0 To NumberOfElements - 1

            City = xmlDoc.SelectSingleNode("//Cities/City").Attributes.getNamedItem("ZIP").Text

            City = City & " " & xmlDoc.getElementsByTagName("City").Item(i).Text

            Tabelle2.Cells(i + 3, 1).Value = City

        Next i

    End If

End Sub

I get all Innertextes from the Elements "City". But everytime the same Attribute "8355".

我从Elements“City”获得了所有Innertextes。但每次都是相同的属性“8355”。

City = xmlDoc.SelectSingleNode("//Cities/City").Attributes.getNamedItem("ZIP").Text

This line should be different, but I don't know how I can loop threw the whole XML to read every single Attrbute.

这行应该是不同的，但我不知道如何循环抛出整个XML来读取每一个Attrbute。

1 个解决方案

#1

xmlDoc.SelectSingleNode("//Cities/City") always selects the first node. It cannot magically select the next node every time, it would have to read your mind for that.

xmlDoc.SelectSingleNode（“// Cities / City”）始终选择第一个节点。它不能每次都神奇地选择下一个节点，它必须为此阅读你的想法。

Private Sub Workbook_Open()
  Dim City As String
  Dim xmlUrl As String
  Dim xmlDoc As New DOMDocument
  Dim n As IXMLDOMNode
  Dim i As Long

  xmlUrl = "http://localhost:62231/dataHandling.asmx/GetAllCities"
  xmlDoc.async = False

  If Not xmlDoc.Load(xmlUrl) Then
    MsgBox "XML LOAD ERROR"
  Else

    For Each n In xmlDoc.selectNodes("//Cities/City")
      City = n.Attributes.getNamedItem("ZIP").Text

      City = City & " " & n.Text

      Tabelle2.Cells(i + 3, 1).Value = City
      i = i + 1
    Next

  End If

End Sub