XML Foreign Exchange rate feed ... into CF array?

Folks:
For me ... this is a tough one! I hope you can help!

I found an online rss xml feed for foreign currency exchange rates. I need to parse the xml feed into an array ...
so I can then import into a mysql table (which I feel pretty good about accomplishing).

BUT I've NO idea how XML stuff works (excuse pretty huge ignorance) ...

I'm hoping to do this:

- read XML file (see link) into these variables:
: exch_rate_date = (the date of the published rates (i.e. 2004-04-23))

: exch_rate_ccy [1,2,3 ... 6,7,8] = 'Malaysia' / 'Hong Kong' etc.
: exch_rate_num [1,2,3 ... 6,7,8] = 0.7287 etc (as a numeric with 4 decimal places)

That way ... I can then do a simple 'insert' into my database of the array contents.

http://www.ny.frb.org/markets/fxrates/FXtoXML.cfm?FEXdate=2004%2D04%2D23%2000%3A00%3A00&FEXtime=1200



Thanks for any guidance!
Richard Buchanan
The Opinion Exchange
rcbuchananAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PE_CF_DEVConnect With a Mentor Commented:
If you are using CF MX then you can use chttp to get that XML return. then use XMLParse(cfhttp.filecontent). This will pout the XML into a ColdFusion XML object. From there you would just access info in the XML like a structure. So you would do

<cfscript>
Exchange_Rates = xmlparse(cfhttp.filecontent);
Country = Exchange_Rates.RateList.rate[1].Country.xmltest;
Rate = Exchange_Rates.RateList.rate[1].Value.xmltext;
</cfscript>

That would return the first rate in the XML response that being Australia as the country and 0.7287 as the rate.

You loop it if you want..something like this
<cfloop from=1 to=#arraylen(Exchange_Rates.RateList.Rate)# step=1 index = 'Rate_Loc'>
<cfset Cur_Rate = Exchange_Rates.RateList.Rate[Rate_Loc]>
<!--- Then you will reference Cur_Rate.whatever to get the values you want into an array --->
</cfloop>
 
If you need mroe explaination let me know
0
 
rcbuchananAuthor Commented:
OK, ... I'm an IDIOT .. and found an EVEN better (more comprehensive list of currency exchange rates (in canadian dollar terms but) ... and i'm EVEN MORE stuck!

I found an online csv feed for foreign currency exchange rates. I need to parse the seven (7) days of rates into my mysql database table.  I understand the database stuff ... BUT I've little clue about parsing csv files! (excuse pretty huge ignorance) ...

I'm hoping to do this:

- read first line (DATE ..) and store the SEVEN days reported into a simple array:
ccy_date [1,2,3 ... 7] = where each contains the date reported

- read the United States Dollar rates into ccy_USD [1,2,3 ... 7] array as numeric /w four decimal places so I can convert the Canadian Dollar reporting into USD reporting.

THEN ...

read EACH subsequent LINE (currency) as an INSERT into my database ...
insert into ccy_table
(ccy_name, ccy_date, ccy_rate)
values ( the csv'd country name, the csv'd date, the csv'd exch rate)

obviouly I'd use the ccy_USD [x] array field to convert the Canadian Dollar influence into the US Dollar spin.

the CSV rates are via:
http://www.bankofcanada.ca/en/financial_markets/csv/exchange_eng.csv



What do you think? [am trying to reload question with another 500 points ... be patient with me!]

Richard

0
 
rcbuchananAuthor Commented:
ok, ... am listing as new question 'cos I cannot see how to reassign new points to it! ... yikes.

Richard
0
All Courses

From novice to tech pro — start learning today.