Assistance with parsing XML from an online web service using Excel VBA-CodePudding

I am not very familiar with programming and know enough to get by through web searching and YT. But this has me lost.

I am attempting to parse an online database to obtain information related to amateur radio callsigns. In this instance the code below is intended to call the database and "login" to get a session ID.

I have formatted the code based on a YT video using Googlemaps as an example. I can create the URL and submit it, view the response text and verify I have a session ID. I can query for the first child node and read that value and from there it stops.

When I run the code I get this:

The request was OK QRZDatabase

I cannot get past this point to read the interior node. At this point I'm lost.

This is the format of the XML

<QRZDatabase xmlns="http://xmldata.qrz.com" version="1.34">
<Session>
<Key>c46e1c7e880208b6e324b229083b9d4d</Key>
<Count>4246</Count>
<SubExp>Mon Oct 17 23:51:57 2022</SubExp>
<GMTime>Mon Oct 25 23:49:17 2021</GMTime>
<Remark>cpu: 0.194s</Remark>
</Session>
</QRZDatabase>

My code:

Sub test()

'Create Request

Dim xml_Obj As MSXML2.XMLHTTP60
Set xml_Obj = New MSXML2.XMLHTTP60
Dim baseURL As String
Dim uName As String
Dim pWord As String
Dim sURL As String

'build the URL components

baseURL = "http://xmldata.qrz.com/xml/current/?"
uName = "username=w4gfq;"
pWord = "password=********"

'sURL = "http://xmldata.qrz.com/xml/current/?username=w4gfq;password=*****"
sURL = baseURL   uName   pWord
'Debug.Print sURL

xml_Obj.Open "GET", sURL, False
xml_Obj.send

Debug.Print "The request was "   xml_Obj.StatusText
'Debug.Print xml_Obj.responseText

'Create Document

Dim XDoc As MSXML2.DOMDocument60
Dim xNodes As IXMLDOMNodeList
Dim xNode As MSXML2.IXMLDOMNode

Set XDoc = New MSXML2.DOMDocument60

XDoc.LoadXML (xml_Obj.ResponseText)

'Debug.Print xml_Obj.ResponseText
Debug.Print XDoc.ChildNodes.Item(1).BaseName

Set xNodes = XDoc.SelectNodes("QRZDatabase/Session")

For Each xNode In xNodes
    Debug.Print "----------------"
    Debug.Print xNode.SelectSingleNode("Key").Text
Next


End Sub

CodePudding user response：

Since your XML has a namespace, I believe your XPath needs to deals with it as well.

Try the code below:

Sub test()
    
    'Create Request
    
    Dim xml_Obj As MSXML2.XMLHTTP60
    Set xml_Obj = New MSXML2.XMLHTTP60
    Dim baseURL As String
    Dim uName As String
    Dim pWord As String
    Dim sURL As String
    
    'build the URL components
    
    baseURL = "http://xmldata.qrz.com/xml/current/?"
    uName = "username=w4gfq;"
    pWord = "password=********"
    
    'sURL = "http://xmldata.qrz.com/xml/current/?username=w4gfq;password=*****"
    sURL = baseURL   uName   pWord
    'Debug.Print sURL
    
    xml_Obj.Open "GET", sURL, False
    xml_Obj.send
    
    Debug.Print "The request was "   xml_Obj.StatusText
    
    'Create Document
    Dim XDoc As MSXML2.DOMDocument60
    Dim xNodes As MSXML2.IXMLDOMElement
    Dim xNode As MSXML2.IXMLDOMElement
        
    Set XDoc = New MSXML2.DOMDocument60
    XDoc.LoadXML xml_Obj.responseText
    XDoc.SetProperty "SelectionNamespaces", "xmlns:qrz=""http://xmldata.qrz.com"""
    
    Set xNodes = XDoc.DocumentElement.SelectSingleNode("qrz:Session")
    For Each xNode In xNodes.ChildNodes
        Debug.Print "----------------"
        Debug.Print xNode.BaseName & ": " & xNode.Text
    Next

End Sub