Unable to select node data from XML

Lenny Gray
Lenny Gray used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

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?
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks for the advice, Scott. Where is the comment editor?
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:

Author

Commented:
Thanks, Bill !
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
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

Author

Commented:
Got it ! Thanks for your help!
Try this:
strDistance = objRoot.selectSingleNode("//distance").text

Open in new window

Author

Commented:
WOW !  Thank You !!!
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

Author

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>
You can modify your XPath query to get any node, like:
strDistance = objRoot.selectSingleNode("//distance/value").text

Open in new window

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

Open in new window

Author

Commented:
strDistance = objRoot.selectSingleNode("//distance").firstChild.text             Worked!!!!

Thanks!!!

Author

Commented:
Outstanding assist!!!

Thanks!

Author

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

Author

Commented:
Last child worked. So, you led me to the correct solution.

Thanks again!!

Author

Commented:
strDistance = objRoot.selectSingleNode("//distance").lastChild.text
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

Author

Commented:
This was great teamwork!  Thanks for teaching me something new!
You are welcome!
Most Valuable Expert 2012
Top Expert 2014

Commented:
Where is the comment editor?
It's the box where you type your comments, or where you enter your question originally.

Author

Commented:
Thanks, Scott. Bill Prew showed me and even transferred my code.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial