Reading selective contents from an XML file using an HTTP connection 1

[gautammalkani]

Hi All

I can read an XML file from a web browser (for user& password verifcation for online users) but I would like to read in the contents from a particular element. Here is my attached code and the XML file:

Sub connectToHTTP()
Dim xml
Set xml = CreateObject("Microsoft.XMLHTTP")
' Opens the connection to the remote server.
xml.Open "GET", "

https://wireqa.pg.com/xgdsauth/xlogon.plx?xusr=username&pwd=password",

False
' Actually Sends the request and returns the data:
xml.send
Range("A20").Value = xml.responseText
End Sub

How do i read in a particular element ie message code since I want to query on a particular value?

XML FILE:
<?xml version="1.0" encoding="UTF-8" ?>
- <logon-service>
- <results>
<message-code>-1</message-code>
<message-text>Invalid Logon</message-text>
<sid>NULL</sid>
</results>
</logon-service>


Appreciate any help. Cheers

Gautam

 

[niceguymattx]

Theres at least a few ways I can think of doing what you want, and depending on the level of useage, and your vba skills it would depend.

If the XML string was used a lot and very dynamically I would be tempted to create an object that could parse and allow you to work with as any other object.

However, from the looks of your post a simple function like the one below should do what you want:

XMLString is the xmlstring returned to you, i.e. xml.responseText

And sTagName refers to the tag who's contents you wish to return, i.e. "<message-code>" (needs to be spelled how it is in the XML file, and including the '<' and '>' )

Any other questions on how to use it just ask

Thanks

Matt

Public Function TagValue(XMLString As String, sTagName As String) as Variant
  Dim iStart As Integer, iLength As Integer
  Dim sCloseTagName As String
  sTagName = "<message-code>"
  sCloseTagName = Left(sTagName, 1) & "/" & Mid(sTagName, 2)
  iStart = InStr(1, XMLString, sTagName) + Len(sTagName)
  iLength = (InStr(1, XMLString, sCloseTagName)) - iStart
  TagValue = Mid(XMLString, iStart, iLength)
End Function

 

[gautammalkani]

thanks for your help

That worked

Cheers

Gautam