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?
 
zc2Connect With a Mentor Commented:
Or you can traverse the found element, like
strDistance = objRoot.selectSingleNode("//distance").firstChild.text

Open in new window

0
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.