create or replace
function get_rss(p_url in varchar2)
return clob
is
l_part utl_http.html_pieces;
l_feed clob:='';
begin
l_part := utl_http.request_pieces(p_url);
for i in 1 .. l_part.count loop
l_feed:= l_feed ||l_part(i);
end loop;
return l_feed;
end get_rss;
/
select xmlquery( 'for $i in //item
return {$i/title}{xs:string($i/pubDate)}{$i/link}
'
passing by value xmltype(feed)
returning content
)
from ( select get_rss('http://technology.amis.nl/blog/?feed=rss2') feed
from dual
)
/
select title
, link
, author
, to_date(substr(publication_date,6,20),'dd mon yyyy hh24:mi:ss') timest
from xmltable(XMLNAMESPACES('http://purl.org/dc/elements/1.1/' AS "dc"),'for $i in //item
return {$i/title}{$i/dc:creator}{$i/pubDate}{$i/link}
'
passing xmltype(get_rss('http://technology.amis.nl/blog/?feed=rss2'))
COLUMNS
title varchar2(100) path 'title'
, link varchar2(100) path 'link'
, author varchar2(100) path 'dc:creator'
, publication_date varchar2(100) path 'pubDate'
)
select title
, link
, author
, to_date(substr(publication_date,6,20),'dd mon yyyy hh24:mi:ss') timest
from xmltable(XMLNAMESPACES('http://purl.org/dc/elements/1.1/' AS "dc"),'for $i in //item
return {$i/title}{$i/dc:creator}{$i/pubDate}{$i/link}
'
passing feed
COLUMNS
title varchar2(100) path 'title'
, link varchar2(100) path 'link'
, author varchar2(100) path 'dc:creator'
, publication_date varchar2(100) path 'pubDate'
)
, (select xmltype(get_rss('http://technology.amis.nl/blog/?feed=rss2')) feed from dual)
select feed_name
, title
, link
, author
, to_date(substr(publication_date,6,20),'dd mon yyyy hh24:mi:ss') timest
from (select 'AMIS Weblog' feed_name
, xmltype(get_rss('http://technology.amis.nl/blog/?feed=rss2')) feed
from dual
union all
select 'OTN Headlines' feed_name
, xmltype(get_rss('http://www.oracle.com/technology/syndication/rss_otn_news.xml')) feed
from dual
union all
select 'OraBlogs' feed_name
, xmltype(get_rss('http://blogs.oracle.com/readingLists/oracleblogs.xml')) feed
from dual
) feeds
, xmltable( XMLNAMESPACES('http://purl.org/dc/elements/1.1/' AS "dc")
, 'for $i in //item
return {$i/title}{$i/dc:creator}{xs:string($i/pubDate)}{$i/link}
'
passing feeds.feed
COLUMNS
title varchar2(100) path 'title'
, link varchar2(100) path 'link'
, author varchar2(100) path 'dc:creator'
, publication_date varchar2(100) path 'timestamp'
)
order
by timest desc
/
select title
, author
, publication_date
, url
, feedname
from rss_collection
where instr(title, 'TOPIC') > 0
order
by publication_date desc