Unable to select node data from XML

I am having trouble pulling the miles from the XML. What am I doing wrong?


Below is the XML code that is returned by Google maps:

<?xml version="1.0"?>
<DistanceMatrixResponse>
    <status>OK</status>
    <origin_address>165 Kingsley St, Long Branch, NJ 07740, USA</origin_address>
    <destination_address>1600 Pennsylvania Ave NW, Washington, DC 20500, USA</destination_address>
    <row>
        <element>
            <status>OK</status>
            <duration>
                <value>13077</value>
                <text>3 hours 38 mins</text>
            </duration>
            <distance>
                <value>348882</value>
                <text>217 mi</text>
            </distance>
        </element>
    </row>
</DistanceMatrixResponse>

Open in new window

This is my Class module code for the form that sends the parameters:

Private Sub cmdCalculate_Click()

    Me.IDistance = GetDistance(Me.FromAddress, Me.ToAddress)

End Sub


'***********************************************************************
'* Fetches Google map query results, returning travel time and mileage *
'***********************************************************************


Public Function GetDistance(origin_address As String, destination_address As String)

Dim strURL              As String
Dim oXH                 As Object
Dim strBodytxt          As String
Dim strDistance         As String
Dim dblRoundTrip        As Double
Dim docDOM              As DOMDocument60
Dim objRoot             As IXMLDOMElement
Dim TheContents         As String
    
    '***********************************************
    '* Assemble the origin and destination strings *
    '***********************************************
    strURL = "http://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & _
    Replace(Replace(Replace(origin_address, " ", "+"), ",", "+"), "++", "+") & _
    "&destinations=" & _
    Replace(Replace(Replace(destination_address, " ", "+"), ",", "+"), "++", "+") & _
    "&mode=driving&sensor=false&units=imperial"
    
    Set oXH = CreateObject("msxml2.xmlhttp")

    '*********************************
    '* Get the Google results string *
    '*********************************
    With oXH
        .Open "get", strURL, False
        .send
        strBodytxt = .responseText
    End With
        
        
    Set docDOM = New DOMDocument60
    docDOM.resolveExternals = False
    docDOM.validateOnParse = False
    
    '*****************************
    '* Load the XML into the DOM *
    '*****************************
    docDOM.loadXML (strBodytxt)
    
    '********************
    '* Find the mileage *
    '********************
    
    TheContents = docDOM.XML
    
    Debug.Print TheContents     ' check to see if the XML got there
    
    Set objRoot = docDOM.documentElement
    strDistance = objRoot.selectSingleNode("Distance").childNodes("text").text
    dblRoundTrip = CDbl(Mid(strDistance, 1, (Len(strDistance) - 3)))  ' One Way

    '**********************
    '* Calculte roundtrip *
    '**********************
    GetDistance = dblRoundTrip * 2

    Set oXH = Nothing
    Set objRoot = Nothing
 
End Function

Open in new window

Thanks, in advance, for your help.

Lenny
Lenny GrayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zc2Commented:
This line:
strDistance = objRoot.selectSingleNode("Distance").childNodes("text").text

Open in new window

Assumes the node "Distance" (with capital "D") is the root node of the XML.

To locate the "distance" element, you should do:
strDistance = objRoot.selectSingleNode("//distance").childNodes("text").text

Open in new window

or, for performance sake:
strDistance = objRoot.selectSingleNode("/DistanceMatrixResponse/row/element/distance").childNodes("text").text

Open in new window

0
Lenny GrayAuthor Commented:
Thanks for the fast response!!

I entered your solution and I am getting a run-time error '13": Type mismatch with either solution.

Any ideas?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, it's a good idea to put very long snippets of code in a "code block" for easier readability. To do that, highlight the code and select the code option from the menu immediately above the comment editor.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Lenny GrayAuthor Commented:
Thanks for the advice, Scott. Where is the comment editor?
0
Bill PrewCommented:
0
Lenny GrayAuthor Commented:
Thanks, Bill !
0
Bill PrewCommented:
It's a little out of date, but you should get the idea.  You can either use the "CODE" icon on the comment editor toolbox, or just manually enclose the pasted code with [code] and [/code].


»bp
1
Lenny GrayAuthor Commented:
Got it ! Thanks for your help!
0
zc2Commented:
Try this:
strDistance = objRoot.selectSingleNode("//distance").text

Open in new window

0
Lenny GrayAuthor Commented:
WOW !  Thank You !!!
0
zc2Commented:
Your code is not optimal. XMLHTTP provides you an XML tree object in .responseXML, so you don't need to load the XML text to a separate DOM tree object. So, instead of
 With oXH
        .Open "get", strURL, False
        .send
        strBodytxt = .responseText
    End With
       
       
    Set docDOM = New DOMDocument60
    docDOM.resolveExternals = False
    docDOM.validateOnParse = False
   
    '*****************************
    '* Load the XML into the DOM *
    '*****************************
    docDOM.loadXML (strBodytxt)

Open in new window

You can do just
 With oXH
        .Open "get", strURL, False
        .send
        Set docDOM = .responseXML
    End With

Open in new window

0
Lenny GrayAuthor Commented:
zc2 that produces the following result:

?strDistance
348882 217 mi

It is picking up everything in <distance>
 <distance>
                <value>348882</value>
                <text>217 mi</text>
            </distance>
0
zc2Commented:
You can modify your XPath query to get any node, like:
strDistance = objRoot.selectSingleNode("//distance/value").text

Open in new window

0
zc2Commented:
Or you can traverse the found element, like
strDistance = objRoot.selectSingleNode("//distance").firstChild.text

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lenny GrayAuthor Commented:
strDistance = objRoot.selectSingleNode("//distance").firstChild.text             Worked!!!!

Thanks!!!
0
Lenny GrayAuthor Commented:
Outstanding assist!!!

Thanks!
0
Lenny GrayAuthor Commented:
zc, I got results, so I thought that it worked. But I was wrong.

It picked up the first three digits in value under distance
0
Lenny GrayAuthor Commented:
Last child worked. So, you led me to the correct solution.

Thanks again!!
0
Lenny GrayAuthor Commented:
strDistance = objRoot.selectSingleNode("//distance").lastChild.text
0
zc2Commented:
I guess the following line truncates the value to the first 3 digits:
blRoundTrip = CDbl(Mid(strDistance, 1, (Len(strDistance) - 3)))  ' One Way

Open in new window

0
Lenny GrayAuthor Commented:
This was great teamwork!  Thanks for teaching me something new!
0
zc2Commented:
You are welcome!
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Where is the comment editor?
It's the box where you type your comments, or where you enter your question originally.
0
Lenny GrayAuthor Commented:
Thanks, Scott. Bill Prew showed me and even transferred my code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.