如何使用 VBA 从 XML 中读取所有属性? [英] How I can read all Attributes from a XML with VBA?

问题描述

我从 Web 服务获得了一个 XML.我将其声明为DOMDocument".这是我的 XML.现在我想读取所有名为ZIP"的属性.

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".

<?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>

随着...

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

我从元素城市"中获得了所有的内文.但每次都是相同的属性8355".

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

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

这一行应该有所不同，但我不知道如何循环抛出整个 XML 以读取每个属性.

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

推荐答案

xmlDoc.SelectSingleNode("//Cities/City") 总是选择第一个节点.它不能每次都神奇地选择下一个节点，它必须为此读取您的想法.

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.

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

这篇关于如何使用 VBA 从 XML 中读取所有属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！