Read XML Attribute VBA

Question

I am trying to get the attribute of a single node in VBA, but can't manage it using DOM

The XML looks like following:

    <?xml version="1.0" encoding="utf-8"?>
       <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
          <soap:Body>
             <GetUserInfoResponse xmlns="http://schemas.microsoft.com/sharepoint/soap/directory/">
                <GetUserInfoResult>
                   <GetUserInfo>
                      <User ID="16" Name="David" LoginName="login" Email="" Notes="" IsSiteAdmin="False" IsDomainGroup="False" />
                      <User ID="17" Name="Gal" LoginName="login" Email="" Notes="" IsSiteAdmin="False" IsDomainGroup="False" />
                      <User ID="18" Name="Netzer" LoginName="login" Email="" Notes="" IsSiteAdmin="False" IsDomainGroup="False" />
                      <User ID="3" Name="More" LoginName="login" Email="" Notes="" IsSiteAdmin="False" IsDomainGroup="False" />
                      <User ID="20" Name="Alon" LoginName="login" Email="" Notes="" IsSiteAdmin="False" IsDomainGroup="False" />
                      <User ID="21" Name="Dan" LoginName="login" Email="" Notes="" IsSiteAdmin="False" IsDomainGroup="False" />
                      <User ID="708" Name="Yaron" LoginName="login" Email="" Notes="" IsSiteAdmin="False" IsDomainGroup="False" />
                </GetUserInfo>
             </GetUserInfoResult>
          </GetUserInfoResponse>
       </soap:Body>
    </soap:Envelope>

I am basically just trying to get the value of the ID attribute. Any help would be appreciated.

this is same question as in the following link: Read XML Attribute VBA with small change ! as you can see in XML file there is more then one USER so the following answer doesn't fit,

Try:

(Include a reference to Microsoft XML v3, I saved your xml to a file on my desktop)

    Dim xmlDoc As DOMDocument30
    Set xmlDoc = New DOMDocument30
    xmlDoc.Load ("C:\users\jon\desktop\test.xml")

    Dim id As String
    id = xmlDoc.SelectSingleNode("//GetUserInfo/User").Attributes.getNamedItem("ID").Text

how can i know the name of user that his ID is "21"?

really be glad if someone can help me

Get the GetUserInfo node first, then select from the subnodes. — Comintern
– Comintern, Commented Dec 14, 2016 at 16:45
@Comintern I don't get it, can you please explain your answer? or give an example ? — David
– David, Commented Dec 14, 2016 at 16:55

Tim Williams · Accepted Answer · 2016-12-18 16:53:15Z

2

This worked for me:

Dim oDoc As New MSXML2.DOMDocument30
Dim el As Object
Dim XML As String

XML = ActiveSheet.Range("B1").Value 'for testing....

oDoc.validateOnParse = True
oDoc.LoadXML XML  '<< using LoadXML to load from a string
                  '   use Load if you're reading from a file

'select the User node with ID=21
Set el = oDoc.SelectSingleNode("//GetUserInfo/User[@ID=""21""]")

If Not el Is Nothing Then
    Debug.Print el.getAttribute("Name")
Else
    Debug.Print "user id not found!"
End If

edited Dec 18, 2016 at 16:53

answered Dec 14, 2016 at 17:13

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

oDoc.LoadXML XML --->oDoc.Load XML

Tim Williams Over a year ago

My answer doesn't need editing: I'm using your XML stored in a worksheet cell and using LoadXML to load the document from that string. Load is correct if you want to load the document from a file.

Collectives™ on Stack Overflow

Read XML Attribute VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related