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 <Article>{$i/title}<timestamp>{xs:string($i/pubDate)}</timestamp>{$i/link}</Article>
                 ' 
                 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 <Article>{$i/title}{$i/dc:creator}{$i/pubDate}{$i/link}</Article>
                 ' 
                 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 <Article>{$i/title}{$i/dc:creator}{$i/pubDate}{$i/link}</Article>
                 ' 
                 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 <Article>{$i/title}{$i/dc:creator}<timestamp>{xs:string($i/pubDate)}</timestamp>{$i/link}</Article>
                 '
                 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