Import ECB XML

[Stuart Taylor]

Hello,

I have been trying to get this XML data:

http://www.ecb.int/stats/eurofxref/eurofxref-sdmx.xml

into a table called "Exchange_Rate" with the fields:

1. Currency_ISO_Alpha_3 (eg: GBP,EUR,USD)

2. Value (eg: 1.4647858)

3. Date_as_Text (eg: 17-01-2007)

(one exchange rate per record)

I just can't get my head around the xsl needed to achieve this.

I would ideally like to be able to query the data for specific date ranges.

I would really appreciate any help with this and would love a an understanding of the components of the xsl sheet that is required in order to attempt this myself in future.

best

Stuart

[IdealData]

Stuart

If you make an XML export from an existing FMP file this will give you a clue.

I'm no expert here.

[ino]

This will solve your problem

http://www.filemaker.com/products/technologies/xslt_library.html

[color:blue]Euro Exchange Rates | Download

by Christian Jaeger

works fine

Ino

[comment]

Hey Stuart,

First, a 60k+ records file is not a good choice for making experiments. Next, Filemaker's XML implementation is pitifully outdated - I think the source you are trying to import may be too advanced for it (I had a similar problem with another data source, and all I managed to get out of it was a spinning beach ball or a crash).

Try this URL instead:

http://www.ecb.int/stats/eurofxref/eurofxref-hist.xml

The XSL required is very simple - see attached.

I don't think you can query a remote XML source. AFAIK, all you can do is import it. Presumably, you could use the XSL sheet to filter out some of the data, but the sheet is a static text document. So to make a dynamic query, you'd need to modify the XSL somehow. For example, you could keep it in a FM calculation field, export it, then use it to import the actual records.

Archive.zip

[Stuart Taylor]

IdealData ... thanks, but although this is of course a good starting point (and you did not know my starting point) it is the xsl that i have trouble understanding not the resulting format required for import. The problems that i have had is how you arrive at the xsl to transform one format to another.

ino ... thanks, i have been aware of this solution since version 6. Good as it may be it is pretty useless in the context of a true relational database structure as it uses repeating fields. As a beginner to XSL i find it extremely confusing when trying to understand what is actually going on in the XSL sheet. I think this solution only ever served to confuse me further.

Comment ... WOW!!!

This is perfect...

Everything i have never seen anywhere else..

I really wasn't expecting the actual solution but not only that i can actually begin to understand the syntax and even begin to understand the other solution that had confused me so much.

Many Many Thanks for your time and effort.

I deduce the "/*/*" is a way of saying 2 levels deep without actually having to name the tags...

and ".../" is one tag level higher that the level specified.

I really can't thank you enough.

I will try and look into date ranges and post any findings.

best wishes,

Stuart

[Stuart Taylor]

Hi Comment,

How right you are...

I put together an xsl sheet that downloads a specific date...

then

I put together an xsl sheet that downloads a range of dates...

then

I created a custom function to calculate the xsl for a specific date range.

then

I tried to export that field to file ... worked ... then import the xml ...

All i get is the "Rainbow wheel of death" and then a crash.

Very disappointing..

Gonna have to download all of the values every day.

How very annoying. (filemaker need to address this)

Will try and find a work around.

best

Stuart

[Fenton]

What was the xml file that crashed FileMaker? Was there an xsl file, or were just trying to come straight back into FileMaker? Can you post a (reasonably-sized) example, zipped? (Don't copy/paste here, as that ruins the test of the encoding.) Or was the path wrong to either the xml file or the xsl file. It is true that FileMaker's error-checking is not as forgiving with xml imports. I've also crashed when doing that. But it was usually because there was something wrong. If the encoding of the file is utf-16 I believe it will crash, like an instant quit, with no message at all.

[comment]

The same question in another form:

I put together an xsl sheet that downloads a specific date...

then

I put together an xsl sheet that downloads a range of dates...

Did these two work - before you went on to generate the XSL document from within Filemaker?

[Stuart Taylor]

I will post an example over the weekend ... yes the stylesheets worked when on there own ...

Lets hope i screwed up the xsl when exporting the calculated field contents...

I know you didn't want the code fenton but as i will not get time to post until Sunday here is the "simple" but relevent extract to pull in a specific date, so that you can have a play yourself before then.

[color:red]IMPORTANT: ANYONE wishing to USE or MODIFY this code extract must download "Comments" files found in this thread and adhere to the agreement found in the xsl file. DOWNLOAD Archive.zip - Copyright Comment.

If you repeat this twice ... three times ... etc... with different dates it will only pull in those dates.

Its a bit bulky but it works and there seems to be no way to specify multiple criteria (OR) within the if line.

best

Stuart

[comment]

If it worked standalone, then Fenton's guess - UTF-16 instead of UTF-8 - is the most likely suspect.

Try a regular export from a single record (i.e. found set of 1).

Edited January 20, 2007 by Guest

[Stuart Taylor]

Thanks to you both.

I will try this and post the results.

best

Stuart

[Fenton]

Though it's not very fast, you can just use " or " in the test:

[Stuart Taylor]

Thanks Fenton,

This is definitely an improvement.

After not being able to find anything about adding an OR i had tried:

and

but of course missed the one variation that would work.

hehe.

Great

[Stuart Taylor]

Ooooops!

Just found a bug/error in the xsl that was exported ... so hold off on the UTF-# for a bit, until i can test it.

[DukeS]

Does anyone know the easiest way to write date range for this xml in the xsl file? I realy can't figure it out. It looks kinda alien language to me...

I tried with this but it doesn't work ( returns empty set ):

Thanks

[Fenton]

The problem is that the value '2007-01-01' is text. If you translate it to a number it will work. But first it would be best to remove the dashes. Even though it's not a date as number as you'd have in FileMaker, it will still work. Because 20070101 is less than 20070105, as long at the month is always padded with a 0 when necessary.

Edited February 19, 2007 by Guest

[DukeS]

Thank you very much, Fenton! It is working great!

One more question. Now if statement returns values between both dates ( 2, 3, 4 ). Is there and option to include also 1 and 5 like >= and <=? If not I will create calc in FM and deviate limit values by one and it should work.

Thanks again

[Fenton]

(P.S. Sometimes on a post here you have to hit Edit, redo your <= symbols, then Submit Change. Otherwise it's just <=, even when it's enclosed in code tags.)

Edited February 19, 2007 by Guest
<=

[Stuart Taylor]

Hi Fenton,

Thanks for all of your work on this.

best,

Stuart

[Fenton]

You're welcome. I didn't know how to do the range thing either, until someone asked; simply never tried it before. We all learn from good questions.

[DukeS]

Thank you Fenton, you are XSL master .

[cjaeger]

why not have a look at my exaple at www.filemaker.com/xml?

It' all done.

ckj

[Stuart Taylor]

Hi Christian,

Your example is of course great (really great actually) and i had a very good look at it before posting this topic.

The issue with your solution for me is that the data is imported into repeating fields so it is not possible to build dynamic relationships.

I will have another look as i know that you definitely solved the filter issue, and this maybe what you are refering to.

best wishes,

Stuart