case-insensitive matching in xpath on VBA?

Question

I am trying to get the attribute of a single node in VBA, but can't manage it using DOM when i want it to be case-insensitive

VBA:

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

XML =("C:\........")

oDoc.validateOnParse = True
oDoc.Load XML  


dim Name as String
Name= "yaron"


  'select the User node with Name="Yaron"
    Set el = oDoc.SelectSingleNode("/GetUserInfo/User[@Name='" & Name & "']")
   'Set el = oDoc.SelectSingleNode("/GetUserInfo/User[matches(@Name, 'Name ' ,'i')]")    
   'Set el = oDoc.SelectSingleNode("/GetUserInfo/User[lower-case(@Name)='" & Name & "']")


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

XML:

  <GetUserInfo>
           <User ID="16" Name="DAVID" LoginName="login1"/>
           <User ID="17" Name="GAL" LoginName="login2"/>
           <User ID="18" Name="YARON" LoginName="login3"/>
  </GetUserInfo>

2 lines in comment two different ways i tried after sow solutions in this site, but apparently those solution won't work for **VBA-**Excel**** i get run-time error '-2147467259(80004005); 'unknown method

Answer 1

Since XPATH 2.0 functions are not possible because MSXML does not support XPath 2.0 *grr* the only possibility to be case insensitive with comparing attribute contents will be using translate function.

    Dim oDoc As New MSXML2.DOMDocument60
...
    Set el = oDoc.SelectSingleNode("/GetUserInfo/User[translate(@Name, 'abcdefghijklmnopqrstuvwxyz', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')='" & UCase(sName) & "']")

This needs at least Microsoft XML, v4.0, better Microsoft XML, v6.0. The Microsoft XML, v3.0 (MSXML2.DOMDocument30) will not suit.

If attribute values are uppercase always then

Set el = oDoc.SelectSingleNode("/GetUserInfo/User[@Name='" & UCase(sName) & "']")

will also fit.

Answer 2

You should be able to match it using the lower-case function as you have tried, however the names of the attributes ARE case sensitive, so you need @Name not @name.

Set el = oDoc.SelectSingleNode("/GetUserInfo/User[lower-case(@Name)='" & Name & "']")