Error using XmlToCursor

[StewartUK]

Hi,

I picked up an xml file from the xe.com download site (

http://www.xe.com/dfs/).

I saved it to a file on my computer.

When I tried to import the saved file to a cursor, I got the error:

XML Parse error: Invalid at the top level of the document.
Line 1, Position 1. n:\samples\sample-gbp.xml

I've never used XmlToCursor before. Can anyone advise me what might be wrong?

Thanks,

Stewart

 

[Mike Lewis]

Stewart,

The XML file generated by XE.com is not in a format that can easily be imported into a VFP cursor. If you look at the XML, you'll see that there are a bunch of <header> elements that contain things like copyright notices and time zone info. These are followed by the <currency> elements that contain the actual data you need.

I'm not sure about this, but I don't think that sort of format will work with XMLTOCURSOR(). The Help file says:

The XML must generally conform to a format that can be interpreted as a table in addition to being well-formed. Well-formed XML that is not easily deconstructed into a table format will fail to import.

It might be possible to get round this by parsing out the <header> elements. Another option would be to use something other than XMLTOCURSOR(), for example by making direct calls to the XML parser.

However, did you know that XE.com can also provide its data in old-fashioned CSV files? You might find these easier to work with.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[StewartUK]

Thanks for your help Mike.

Yes, I saw that they provide a csv download as well. I saw the XML and thought that it might be an easier option.

Stewart

 

[Mike Lewis]

Stewart,

Having had a closer look at the XML, I think it would be quite easy to fix it. This is not tested, but it will give you the general idea:

lcXML = FILTOSTR("MyCEFile")
lnPos1 = AT("<header>", lcXML)- 1
lnPos2 = RAT("</header>", lcXML) + 9
lcNewXML = LEFT(lcXML, lnPos1) + SUBSTR(lcXML, lnPos2)
XMLTOCURSOR(lcNewXML, "MyCursor")

On the other hand, it does look a bit kludgy to have to doctor the XML in this way. It seems to defeat the object of XML. Maybe the CSV file would be better after all.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk