Stuart Taylor Posted January 18, 2007 Share Posted January 18, 2007 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 Link to comment Share on other sites More sharing options...
IdealData Posted January 18, 2007 Share Posted January 18, 2007 Stuart If you make an XML export from an existing FMP file this will give you a clue. I'm no expert here. Link to comment Share on other sites More sharing options...
ino Posted January 18, 2007 Share Posted January 18, 2007 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 Link to comment Share on other sites More sharing options...
comment Posted January 18, 2007 Share Posted January 18, 2007 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 Link to comment Share on other sites More sharing options...
Stuart Taylor Posted January 18, 2007 Author Share Posted January 18, 2007 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 Link to comment Share on other sites More sharing options...
Stuart Taylor Posted January 19, 2007 Author Share Posted January 19, 2007 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 Link to comment Share on other sites More sharing options...
Fenton Posted January 20, 2007 Share Posted January 20, 2007 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. Link to comment Share on other sites More sharing options...
comment Posted January 20, 2007 Share Posted January 20, 2007 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? Link to comment Share on other sites More sharing options...
Stuart Taylor Posted January 20, 2007 Author Share Posted January 20, 2007 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 Link to comment Share on other sites More sharing options...
comment Posted January 20, 2007 Share Posted January 20, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
Stuart Taylor Posted January 20, 2007 Author Share Posted January 20, 2007 Thanks to you both. I will try this and post the results. best Stuart Link to comment Share on other sites More sharing options...
Fenton Posted January 20, 2007 Share Posted January 20, 2007 Though it's not very fast, you can just use " or " in the test: Link to comment Share on other sites More sharing options...
Stuart Taylor Posted January 20, 2007 Author Share Posted January 20, 2007 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 Link to comment Share on other sites More sharing options...
Stuart Taylor Posted January 21, 2007 Author Share Posted January 21, 2007 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. Link to comment Share on other sites More sharing options...
DukeS Posted February 15, 2007 Share Posted February 15, 2007 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 Link to comment Share on other sites More sharing options...
Fenton Posted February 19, 2007 Share Posted February 19, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
DukeS Posted February 19, 2007 Share Posted February 19, 2007 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 Link to comment Share on other sites More sharing options...
Fenton Posted February 19, 2007 Share Posted February 19, 2007 (edited) (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 <= Link to comment Share on other sites More sharing options...
Stuart Taylor Posted February 19, 2007 Author Share Posted February 19, 2007 Hi Fenton, Thanks for all of your work on this. best, Stuart Link to comment Share on other sites More sharing options...
Fenton Posted February 19, 2007 Share Posted February 19, 2007 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. Link to comment Share on other sites More sharing options...
DukeS Posted February 20, 2007 Share Posted February 20, 2007 Thank you Fenton, you are XSL master . Link to comment Share on other sites More sharing options...
cjaeger Posted March 3, 2007 Share Posted March 3, 2007 why not have a look at my exaple at www.filemaker.com/xml? It' all done. ckj Link to comment Share on other sites More sharing options...
Stuart Taylor Posted March 3, 2007 Author Share Posted March 3, 2007 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6287 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now