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>
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".
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.
The Attr object represents an attribute of an Element object. The allowable values for attributes are usually defined in a DTD. Because the Attr object is also a Node, it inherits the Node object's properties and methods.
attributes cannot contain multiple values (elements can) attributes cannot contain tree structures (elements can) attributes are not easily expandable (for future changes)
Attribute Types: There are three types of attributes described below: String types Attribute: This type of attribute takes any string literal as a value.
Excel VBA XML. The full form of XML is eXtensible Markup Language which is much like an HTML file, is designed to store and transport the data from different programs. XML file stores the data which includes the splits and separators. We can create a VBA code by which we can import the data from the XML file into Excel ...
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With