How to read XML attributes using VBA to Excel?

问题:

Here is my code..

   <?xml version="1.0" ?> 
   <DTS:Executable xmlns:DTS="www.microsoft.com/abc" DTS:ExecutableType="xyz">
       <DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property> 
       <DTS:Property DTS:Name="VersionComments" /> 
       <DTS:Property DTS:Name="CreatorName">FirstUser</DTS:Property> 
       <DTS:Property DTS:Name="CreatorComputerName">MySystem</DTS:Property>
   </DTS:Executable>

In this I am able to read Elements using "abc.baseName" and its value using "abc.Text". It gives me result as

Property 3 Property
Property FirstUser

In this how can I read "PackageFormatVersion" as 3? i.e., I know some value is 3 but what that value is how could I know??

I mean I have to select which attribute I want to read.

回答1:

Refer either to the element's .Text property or the .nodeTypeValue property :

Sub TestXML()
Dim xmlDoc As Object 'Or enable reference to Microsoft XML 6.0 and use: MSXML2.DOMDocument
Dim elements As Object
Dim el As Variant
Dim xml$
xml = "<?xml version=""1.0"" ?>"
xml = xml & "<DTS:Executable xmlns:DTS=""www.microsoft.com/abc"" DTS:ExecutableType=""xyz"">"
xml = xml & "<DTS:Property DTS:Name=""PackageFormatVersion"">3</DTS:Property>"
xml = xml & "<DTS:Property DTS:Name=""VersionComments"" />"
xml = xml & "<DTS:Property DTS:Name=""CreatorName"">FirstUser</DTS:Property>"
xml = xml & "<DTS:Property DTS:Name=""CreatorComputerName"">MySystem</DTS:Property>"
xml = xml & "</DTS:Executable>"

Set xmlDoc = CreateObject("MSXML2.DOMDocument")
'## Use the LoadXML method to load a known XML string
xmlDoc.LoadXML xml
'## OR use the Load method to load xml string from a file location:
'xmlDoc.Load "C:\my_xml_filename.xml"

'## Get the elements matching the tag:
Set elements = xmlDoc.getElementsByTagName("DTS:Property")
'## Iterate over the elements and print their Text property
For Each el In elements
    Debug.Print el.Text
    '## Alternatively:
    'Debug.Print el.nodeTypeValue
Next

End Sub

I know some value is 3 but what that value is how could I know??

You can review the objects in the Locals window, and examine their properties:

Here is an alternative, which seems clunkier to me than using the GetElementsByTagName but if you need to traverse the document, you could use something like this:

Sub TestXML2()
Dim xmlDoc As MSXML2.DOMDocument
Dim xmlNodes As MSXML2.IXMLDOMNodeList
Dim xNode As MSXML2.IXMLDOMNode
Dim cNode As MSXML2.IXMLDOMNode
Dim el As Variant
Dim xml$
xml = "<?xml version=""1.0"" ?>"
xml = xml & "<DTS:Executable xmlns:DTS=""www.microsoft.com/abc"" DTS:ExecutableType=""xyz"">"
xml = xml & "<DTS:Property DTS:Name=""PackageFormatVersion"">3</DTS:Property>"
xml = xml & "<DTS:Property DTS:Name=""VersionComments"" />"
xml = xml & "<DTS:Property DTS:Name=""CreatorName"">FirstUser</DTS:Property>"
xml = xml & "<DTS:Property DTS:Name=""CreatorComputerName"">MySystem</DTS:Property>"
xml = xml & "</DTS:Executable>"

Set xmlDoc = CreateObject("MSXML2.DOMDocument")
'## Use the LoadXML method to load a known XML string
xmlDoc.LoadXML xml
'## OR use the Load method to load xml string from a file location:
'xmlDoc.Load "C:\my_xml_filename.xml"

'## Get the elements matching the tag:
Set xmlNodes = xmlDoc.ChildNodes
'## Iterate over the elements and print their Text property
For Each xNode In xmlDoc.ChildNodes
    If xNode.NodeType = 1 Then  ' only look at type=NODE_ELEMENT
        For Each cNode In xNode.ChildNodes
            Debug.Print cNode.nodeTypedValue
            Debug.Print cNode.Text
        Next
    End If
Next

End Sub

回答2:

Sub TestXML()
Set Reference to Microsoft XML 6.0
Dim Init As Integer
Dim xmlDoc As MSXML2.DOMDocument
Dim elements As Object
Dim el As Variant
Dim Prop As String
Dim NumberOfElements As Integer
Dim n As IXMLDOMNode
Init = 5

Set xmlDoc = CreateObject("MSXML2.DOMDocument")

xmlDoc.Load ("C:\Users\Saashu\Testing.xml")

Set elements = xmlDoc.getElementsByTagName("DTS:Property")

Prop = xmlDoc.SelectSingleNode("//DTS:Property").Attributes.getNamedItem("DTS:Name").Text

NumberOfElements = xmlDoc.getElementsByTagName("DTS:Property").Length

For Each n In xmlDoc.SelectNodes("//DTS:Property")
   Prop = n.Attributes.getNamedItem("DTS:Name").Text
   Prop = Prop & " :: " & n.Text
   ActiveSheet.Cells(Init, 9).Value = Prop
   Init = Init + 1
Next
End Sub

This code still needs refinement as my requirement is to display only some of those attributes like CreatorName and CreatorComputerName,not all.

Thanks to David,for helping me in this issue.