Oracle XML and XPath

**cfslab08** · Jul 7 '08, 05:30 PM

Hello,

I'm currently trying to extract data from the following xml feed:

http://www.bankofcanada.ca/rss/fx/noon/fx-noon-all.xml

The Bank of Canada is the nation’s central bank. We are not a commercial bank and do not offer banking services to the public. Rather, we have responsibilities for Canada’s monetary policy, bank notes, financial system, and funds management. Our principal role, as defined in the Bank of Canada Act, is "to promote the economic and financial welfare of Canada."

I do the following to get the XML into an XMLType table:

CREATE TABLE cfs_url_table ( url_name VARCHAR2(100), url SYS.URITYPE );

CREATE TABLE cfs_xml_table ( xml_data XMLTYPE );

INSERT INTO cfs_url_table VALUES
('Bank of Canada Exchange rates',
sys.UriFactory. getUri('http://www.bankofcanad a.ca/rss/fx/noon/fx-noon-all.xml')
);

INSERT INTO cfs_xml_table SELECT sys.xmltype.cre ateXML(u.url.ge tClob()) from cfs_url_table u;

At this point, I want to be able to extract data using a query like

SELECT
extractValue(va lue(xml), '/item/title') as title
FROM cfs_xml_table,t able(XMLSequenc e(extract(xml_d ata, '/rdf/item', 'xmlns:rdf="htt p://www.w3.org/1999/02/22-rdf-syntax-nx#"'))) xml;

I can't seem to get this to work. The query will run to completion but I never get any data back. Does anyone have any ideas on how to do this, or what I am doing wrong?

I suspect it has to do with the namespaces but I'm not sure how to deal with that problem.

Thanks,

Devon