HELP!xml file of response from google maps in cursor VFP

[serpico84]

Through a procedure and the Google Maps API have managed to pass the town and the place to go to find the distance between them.
The results were the following XML file:

<?xml version="1.0" encoding="UTF-8" ?>
- <DistanceMatrixResponse>
<status>OK</status>
<origin_address>Bologna, Italia</origin_address>
<destination_address>Milano, Italia</destination_address>
<destination_address>Genova, Italia</destination_address>
- <row>
- <element>
<status>OK</status>
- <duration>
<value>8282</value>
<text>2 ore 18 min</text>
</duration>
- <distance>
<value>220486</value>
<text>220 km</text>
</distance>
</element>
- <element>
<status>OK</status>
- <duration>
<value>11321</value>
<text>3 ore 9 min</text>
</duration>
- <distance>
<value>309019</value>
<text>309 km</text>
</distance>
</element>
</row>
</DistanceMatrixResponse>

How do I import this XML file into a cursor like this:
Departure | Arrival | Duration | Distance

Sorry for my English.
Thank you in advance for your help.

F.

 

[JRB-Bldr]

Since this is so simple a Text file (albeit containing XML 'data') with tags indicating where to find the individual data values, it could very easily be parsed using the FILETOSTR() function.

Then within the resultant string merely fine the desired parameters, insert their string representation into memory variables, and then insert them into a data table/cursor.

Especially if each resultant XML text file only represented a single set of values.

For myself, only if the resultant XML text file had MANY distinct sets of values in it, then I'd look for a different approach.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

You might be able to do it with XMLTOCURSOR(). But that function is quite fussy about the format of the XML.

You could also try the XMLAdapter. Something like this:

loXML = CREATEOBJECT("XMLAdapter")
loXML.LoadXML(FILETOSTR("google.xml"))
  && Google.xml is a text file holding your XML
loXML.tables(1).ToCursor(.F., "csrGoogle")

I haven't tested that with your XML. It might need some adjustment, but it should give you a starting point.

By the way, I think you will need to remove the hyphens at the beginning of some of the lines in the XML. I'm not sure how they got there, but they will probably cause an error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Just a short look at the ml reveals you need to manually retrieve the data from it, eg <distance></distance> has a substructure of <value></value> and <text></text> in it, so it's not convertible to a table field by a simple call of XMLToCursor() or XMLAdapters ToCursor() method.

You can go as far as loading the xml via XMLAdapter, and then inspect the nodes of the XML DOM model this creates.

If you never worked with any DOM model, eg also the Internet Explorer DOM of a html page, you may now start or do as jrbbldr initially suggested and simply see the xml as a piece of text you can work on with string functions, eg StrExtract(), At(), Substr(), Left(), Right(), etc to divide the string into chunks you can extract the values of.

Bye, Olaf.

 

[serpico84]

Hello Mike,
I tried to run your procedure but I get an error:

"Index or expression does not match an existing member of the collection"

This is the procedure used to send the request to the web service to google map:

cAddress1 = "Bologna"
cAddress2 = "Milano | Genova"
LOCAL cUrl, oHTTP, cResult

cUrl= "

http://maps.google.it/maps/api/dist...2+"&mode=driving&language=it-IT&sensor=false"

oHTTP = CreateObject("MSXML2.XMLHTTP")
oHTTP.Open("GET", cURL, .f.)
oHTTP.Send
cResult=(oHTTP.ResponseText)

nCode=GETWORDNUM(cResult,1,",")
nAccuracy=GETWORDNUM(cResult,2,",")
nLat=GETWORDNUM(cResult,3,",")
nLon=GETWORDNUM(cResult,4,",")

STRTOFILE(cResult,'c:\result.xml')

 

[Mike Lewis]

Yes, I think Olaf's right. I just tried running the XML through XMLAdapter. It failed to create any cursors.

It looks like it will be easier to parse the XML with native VFP functions, as Olaf and JRB-Bldr suggested.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

VFP string manipulation is one way to get there, sure. Another way is through the Document Object Model. The MS XML parser (also a class inside MSXML2, which you're already using) presents its own methods for querying an XML document.

See

http://msdn.microsoft.com/en-us/library/aa468547.aspx

Depending on your long term needs, the DOM approach may provide a more robust and growable approach.

 

[Olaf Doschke]

serpico,

in what line of that code does the error occur? Have you got any errorhandler. If not at least do ON ERROR ? LineNo(), Error()

Index in this case is not meaning cdx index, it's rather an element index. You use GetWordnum with a comma seperator to determine the 1.-4. element of cResponse. But there is no comma inside, this fails of course.

In your approach you already have something better inside ohttp than the responsetext, you have the DOM I ans dan talked about already:

oXMLDOM = oHTTP.responseXML

Do that at the commandline, then you have intellisense of oXMLDOM.

eg you can do

...
oXMLDOM = oHTTP.responseXML
oDistanceValueNodes = oXMLDOM.selectNodes("//distance/value")
? oDistanceValueNodes.length

? oDistanceValueNodes.item(0).xml
? oDistanceValueNodes.item(0).text
? oDistanceValueNodes.item(1).xml
? oDistanceValueNodes.item(1).text

d1 = val(oDistanceValueNodes.item(0).text)
d2 = val(oDistanceValueNodes.item(1).text)
? d1,d2

The "magic" of that code is in the selectNodes method and the xpath query used within. It extracts just the xml inside the <value> tags/nodes/items inside the <distance> tags/nodes/items.

val() of the texts is the two distances in meters, it seems.

Instead of hardcoding (0) and (1) you can loop from 0 to oDistanceValueNodes.length-1, length here is not a length but a count, the count of items.

Bye, Olaf.

 

[serpico84]

Thank you thank you thank you thank you Olaf.
I did, thank you very much.
I thank everyone for their availability and their answers.

cAddress1 = "Milano"
cAddress2 = "Bologna | Genova | Francia,Bourges, rue voltaire 2"
LOCAL cUrl, oHTTP, cResult

cUrl= "

http://maps.google.it/maps/api/dist...2+"&mode=driving&language=it-IT&sensor=false"

oHTTP = CreateObject("MSXML2.XMLHTTP")
oHTTP.Open("GET", cURL, .f.)
oHTTP.Send
oXMLDOM = oHTTP.responseXML
* cResult=(oHTTP.ResponseText)


* STRTOFILE(cResult,'c:\result.xml')

oOrigine = oXMLDOM.selectNodes("//origin_address")
IF oOrigine.length != 1 then
MESSAGEBOX('Impossibile..contatta il fornitore di assistenza')
ELSE
ciao= ''
ciao = oOrigine.item(0).text


ENDIF

CREATE CURSOR distance (partenza c(70), arrivo c(70), durata c(15), distanza c(15), stato L)

oDestinazione = oXMLDOM.selectNodes("//destination_address")
oStato = oXMLDOM.selectNodes("//status")
oDurata = oXMLDOM.selectNodes("//duration / text")
oDistanza = oXMLDOM.selectNodes("//distance / text")


FOR i= 0 TO odestinazione.length -1

IF ostato.item(i).text = 'OK' THEN



INSERT INTO distance(partenza, arrivo, durata, distanza,stato) VALUES ;
(ciao,odestinazione.item(i).text ,odurata.item(i).text,odistanza.item(i).text,.T.)
ELSE
INSERT INTO distance(partenza, arrivo, durata, distanza,stato) VALUES ;
(ciao,'INDIRIZZO NON CODIFICATO' ,'','',.F.)
ENDIF
ENDFOR

SELECT distance
BROWSE

 

[Olaf Doschke]

Hi serpico,

you're welcome.

I haven't done much xpath queries myself, but what might be good to know is that you can not only query for xml tag names like shown, that query language also is capable to filter in many other ways, by value, by attribute names or values etc.

//node / subnide is just a starter, there's much more...

http://www.w3schools.com/xpath/xpath_intro.asp

http://www.w3.org/TR/xpath/

Bye, Olaf.