Building an RSS Feed Reader in PL/SQL – using dbms_xmlparser, dbms_xmldom and dbms_xslprocessor for parsing and transforming

This article will demonstrate how we can build an RSS Feed Reader in PL/SQL. We will use DBMS_XMLPARSER to read an RSS feed from an URL and parsethe RSS document, then using the DBMS_XMLDOM package we will analyze the document and then make use of the DBMS_XSLPROCESSOR package to transform the RSS document into a presentable format. 

Many websites today publish RSS feeds. These feeds contain news items, the latest article publications, the most recent entries in discussion forums, software releases etc. The information available from RSS Feeds is structured according to a generic (almost) standard structure. That means that we can easily create software that processes RSS Feeds! Of course many RSS Feed Readers are available, for example as plugins for FireFox or other browsers, for Eclipse (created by my colleague Jeroen, see his article soon to be published on the weblog) or just as stand-alone tool.

In this article we create our own RSS Feed Reader, in PL/SQL of all programming languages!

Some of the RSS Feeds that we can read programmatically:

https://technology.amis.nl/blog/?feed=rss2
http://rss.cnn.com/rss/cnn_topstories.rss
http://newsrss.bbc.co.uk/rss/newsonline_world_edition/front_page/rss.xml
http://www.oracle.com/technology/syndication/rss_otn_news.xml
http://blogs.oracle.com/readingLists/oracleblogs.xml

Retrieve, Parse and Report

We will use the supplied package utl_http to retrieve our RSS document. This package is fed a URL and returns the HttpResponse that is received from the WebServer behind that URL. You could say that using UTL_HTTP the database acts like a Web Browser! Well, actually: we do not even need to use UTL_HTTP ourselves as DBMS_XMLPARSER can parse content from an URL directly! (underneath it will probably leverage utl_http, but we need not bother).

Since we know that the response from an RSS feed returned from the URL is in fact an XML Document, we can parse the text into an XML Document, using the  DBMS_XMLPARSER package.

     rssFeedDoc    dbms_xmldom.domdocument;
   begin
     rssFeedDoc   := dbms_xmlparser.parse(url);
     ...

Now that we have the RSS Feed as parsed XML Document, we can process its contents using the functions and procedures in the DBMS_XMLDOM package:

   rssItemsList:= dbms_xmldom.GETELEMENTSBYTAGNAME(rssFeedDoc, 'item');
   itemNode:= dbms_xmldom.item(rssItemsList,0);
 

It is quite easy to read the relevant node-values into fields of our PL/SQL Record type variable and then write a brief summary from each RSS Feed entry to the output. The entire code block:

declare
 
 type rss_item_type is record
 ( title    varchar2(2000)
 , link     varchar2(200)
 , pubDate  varchar2(200)
 , author   varchar2(200)
 , category varchar2(500)
 , description varchar2(4000)
 );

 url varchar2(500):= 'https://technology.amis.nl/blog/?feed=rss2';
 rssFeedDoc    dbms_xmldom.domdocument;
 itemNode      dbms_xmldom.domnode;
 childnode     dbms_xmldom.domnode;
 myParser      dbms_xmlparser.Parser;
 rssItemsList  dbms_xmldom.domnodeList  ;
 rssItemFields dbms_xmldom.domnodeList  ;
 childNodeText varchar2(32000);
 
 l_rss_item        rss_item_type;
 l_empty_rss_item  rss_item_type;
 
 procedure pl(text in varchar2)
 is
 begin
   if length(text)> 255
   then
     for i in 1..(trunc(length(text)/255)+1) loop
       dbms_output.put_line(substr(text,1+ 255*(i-1),255));
     end loop;
   else
     dbms_output.put_line(text);
   end if;
 end pl;

 procedure report_rss_item(p_rss_item in rss_item_type)
 is
 begin
   pl(p_rss_item.title||' by '||p_rss_item.author);
   pl('Published on: '||p_rss_item.pubDate);
   pl('Category: '||p_rss_item.category);
   pl('==');
   pl('Description: '||p_rss_item.description);
   pl('----------------------------------------');
   pl(' ');
 end report_rss_item;

 
begin
   myParser := dbms_xmlparser.newParser;
   rssFeedDoc   := dbms_xmlparser.parse(url);
   rssItemsList:= dbms_xmldom.GETELEMENTSBYTAGNAME(rssFeedDoc, 'item');
   l_rss_item:= l_empty_rss_item;
   pl('RSS Feed from AMIS Technology Weblog - most recent '||dbms_xmldom.getlength(rssItemsList)||' articles:');
   pl('=================================================================');
   for i in 1..dbms_xmldom.getlength(rssItemsList) loop
     itemNode:= dbms_xmldom.item(rssItemsList,i-1);
     rssItemFields:= dbms_xmldom.getChildNodes(itemNode);
     for j in 1..dbms_xmldom.getlength(rssItemFields) loop
       childnode:= dbms_xmldom.item(rssItemFields, j-1);
       childNodeText:= dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(childnode));
       case dbms_xmldom.getNodeName(childnode)
       when 'title'
       then l_rss_item.title := childNodeText;
       when 'link'
       then l_rss_item.link := childNodeText;
       when 'pubDate'
       then l_rss_item.pubDate := childNodeText;
       when 'dc:creator'
       then l_rss_item.author := childNodeText;
       when 'description'
       then l_rss_item.description := childNodeText;
       when 'category'
       then l_rss_item.category := childNodeText;
       else null;
       end case;             
     end loop;
     report_rss_item(l_rss_item);
   end loop;
end;
/ 

The output of this PL/SQL code for the AMIS Technology WebLog RSS Feed:

RSS Feed from AMIS Technology Weblog - most recent 10 articles:
=================================================================
Building an RSS Feed Reader in PL/SQL - using utl_http, dbms_xmldom and
dbms_xslprocessor for parsing and transforming by Lucas Jellema
Published on: Sat, 29 Apr 2006 18:22:53 +0000
Category: XML
==
Description: This article will demonstrate how we can build an RSS Feed Reader
in PL/SQL. We will use utl_http to read an RSS feed from an URL, parse the RSS
document using the DBMS_XMLDOM package and then make use of the
DBMS_XSLPROCESSOR package to tran
sform the RSS document into a presentable format. Many websites today
publish RSS [...]
----------------------------------------
Getting used to Oracle SQL Developer by Patrick Sinke
Published on: Fri, 28 Apr 2006 15:30:34 +0000
Category: Devel. + PL/SQL tools
==
Description: 
 It took a few months, but I finally start appreciating the
Oracle
SQL Developer developed by Oracle (The Project Formerly Known As
Raptor).
There are still some bugs, for instance with debugging through
a
firewall (hey, didn’t we
have the same problem with JDeveloper?) and
some features are missing, but
it’s quite usable. Especially when there
is no TOAD or [...]
----------------------------------------
Software Deploying using ANT by bertjan
Published on: Thu, 27 Apr 2006 16:13:45 +0000
Category: KC Software Engineering
==
Description: 
  Some time ago a customer of AMIS asked me to setup the aut
omatic
... 

Let’s add a bit of XSL-T transformation to our code.

Using the dbms_xslprocessor package for XSL-T transformations

The presentation of the RSS items has been programmed somewhat clumsily. We do not really benefit from the fact that the RSS Feed is available as XML Document. Using an XSLT stylesheet, we do not need to programmatically extract individual nodes and print their contents to the output.

The XSL-T Stylesheet is first defined in a simple VARCHAR2 variable that subsequently is parsed into an XMLDocument.

 myParser      dbms_xmlparser.Parser;
 proc        dbms_xslprocessor.processor;
 xsltdomdoc  dbms_xmldom.domdocument;
 xsl         dbms_xslprocessor.stylesheet;
 outdomdocf  dbms_xmldom.domdocumentfragment;
 outnode     dbms_xmldom.domnode;
 ...
  xsldoc     varchar2(32000):= 
   '<?xml version="1.0"?> 
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      ...
    </xsl:stylesheet>';
   ...
   -- get XSLT stylesheet in stylesheet type variable
   myParser := dbms_xmlparser.newParser;
   dbms_xmlparser.parseBuffer(myParser, xsldoc);
   xsltdomdoc := dbms_xmlparser.getDocument(myParser);
   xsl        := dbms_xslprocessor.newstylesheet(xsltdomdoc, '');
   

Then we can use the dbms_xmlprocessor to use this stylesheet for transforming the RSS Feed document into a more report-oriented XML document:

 --apply stylesheet to DOM document   
   proc       := dbms_xslprocessor.newProcessor;

The entire block of code:

declare
 

-- url varchar2(500):= 'https://technology.amis.nl/blog/?feed=rss2';
 --url varchar2(500):= 'http://rss.cnn.com/rss/cnn_topstories.rss ';
 url varchar2(500):= 'http://newsrss.bbc.co.uk/rss/newsonline_world_edition/front_page/rss.xml ';
--http://www.oracle.com/technology/syndication/rss_otn_news.xml
--http://blogs.oracle.com/readingLists/oracleblogs.xml


 rssFeedDoc    dbms_xmldom.domdocument;
 itemNode      dbms_xmldom.domnode;
 childnode     dbms_xmldom.domnode;
 myParser      dbms_xmlparser.Parser;
 rssItemsList  dbms_xmldom.domnodeList  ;
 rssItemFields dbms_xmldom.domnodeList  ;
 childNodeText varchar2(32000);
 proc        dbms_xslprocessor.processor;
 
 xsltdomdoc  dbms_xmldom.domdocument;
 xsl         dbms_xslprocessor.stylesheet;
 outdomdocf  dbms_xmldom.domdocumentfragment;
 outnode     dbms_xmldom.domnode;
 buf         varchar2(32000); 
 
  xsldoc     varchar2(32000):= 
   '<?xml version="1.0"?> 
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output encoding="utf-8"/>
<xsl:template match="/">
         <rssItems>
         <xsl:apply-templates select="//item"/>
        </rssItems>
      </xsl:template>      
      <xsl:template match="//item">
         <rssItem>
           <xsl:value-of select="title"/><xsl:text>
           by: </xsl:text>
           <xsl:value-of select="creator"/><xsl:text>
           Published on: </xsl:text><xsl:value-of select="pubDate"/>
           <xsl:text>
           Category: </xsl:text><xsl:value-of select="category"/>
           <xsl:text>
           ==
           </xsl:text><xsl:value-of select="description"/><xsl:text>
           -----------------------------------------------------------------------
           </xsl:text>
           </rssItem>
      </xsl:template>
    </xsl:stylesheet>';
 
 procedure pl(text in varchar2)
 is
 begin
   if length(text)> 255
   then
     for i in 1..(trunc(length(text)/255)+1) loop
       dbms_output.put_line(substr(text,1+ 255*(i-1),255));
     end loop;
   else
     dbms_output.put_line(text);
   end if;
 end pl;

 
 
begin
   rssFeedDoc   := dbms_xmlparser.parse(url);

   -- get XSLT stylesheet in stylesheet type variable
   myParser := dbms_xmlparser.newParser;
   dbms_xmlparser.parseBuffer(myParser, xsldoc);
   xsltdomdoc := dbms_xmlparser.getDocument(myParser);
   xsl        := dbms_xslprocessor.newstylesheet(xsltdomdoc, '');

   
   --apply stylesheet to DOM document   
   proc       := dbms_xslprocessor.newProcessor;
   outdomdocf := dbms_xslprocessor.processxsl(proc, xsl, rssFeedDoc);
   outnode    := dbms_xmldom.makenode(outdomdocf); 
   dbms_xmldom.writetobuffer(outnode, buf);
   pl('RSS Feed from AMIS Technology Weblog - most recent articles:');
   pl('=================================================================');
   pl(buf);
end;
/ 

The results: 

RSS Feed from AMIS Technology Weblog - most recent articles:
=================================================================
<rssItems>
  <rssItem>Building an RSS Feed Reader in PL/SQL - using
dbms_xmlparser, dbms_xmldom and dbms_xslprocessor for parsing and transforming

by: 
           Published on: Sat, 29 Apr 2006 18:22:53 +0000

Category: General

==
           
This article will demonstrate how we can build an RSS Feed Reader
in PL/SQL. We will use DBMS_XMLPARSER to read an RSS feed from an URL and
parsethe RSS document, then using the DBMS_XMLDOM package we will analyze the
document and then
make use of the DBMS_XSLPROCESSOR package to transform the RSS document into a
[...]

-----------------------------------------------------------------------

</rssItem>
  <rssItem>Getting used to Oracle SQL Developer
           by: 

Published on: Fri, 28 Apr 2006 15:30:34 +0000
           Category: Devel. +
PL/SQL tools
           ==
           
&amp;#160;It took a few months, but I
finally start appreciating the Oracle
SQL Developer developed by Oracle (The
Project Former
ly Known As Raptor).
There are still some bugs, for instance with debugging
through a
firewall (hey, didn&amp;#8217;t we have the same problem with
JDeveloper?) and
some features are missing, but it&amp;#8217;s quite usable. 

Web Publishing our RSS Feed using MOD_PLSQL

Using the MOD_PLSQL extension to the Apache Web Server, we can easily create a PL/SQL procedure that publishes the RSS Feed we have read from the Internet URL. Using a proper XSLT-stylesheet, we can turn the RSS XML Document into a pretty presentable HTML page. With the following XSL-T stylesheet:

<?xml version="1.0"?> 
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output encoding="utf-8"/>
<xsl:template match="/">
<h2>RSS Feed from AMIS Technology Weblog - most recent articles:</h2><hr />
         <ul>
         <xsl:apply-templates select="//item"/>
        </ul>
      </xsl:template>      
      <xsl:template match="//item">
<li>
         <xsl:element name="a">
           <xsl:attribute name="target"><xsl:text>_blank</xsl:text></xsl:attribute>
           <xsl:attribute name="href"><xsl:value-of select="link" /></xsl:attribute>
           <xsl:value-of select="title" />
         </xsl:element>
         <span style="font-family:sans-serif;font-size:8pt">
         <xsl:attribute name="title"><xsl:value-of select="description" /></xsl:attribute>
         <xsl:text>( Published on: </xsl:text><xsl:value-of select="pubDate" /><xsl:text>)</xsl:text></span>
       </li>
      </xsl:template>
    </xsl:stylesheet> 

we create the web-page as shown below:

Note how we use the HTML attribute title to show the bubble text whenever the cursor hovers over the publication date.

The procedure rss_reader that we now invoke through the MOD_PLSQL module is as follows:

create or replace
procedure rss_reader
as 

 url varchar2(500):= 'http://newsrss.bbc.co.uk/rss/newsonline_world_edition/front_page/rss.xml ';

 rssFeedDoc    dbms_xmldom.domdocument;
 itemNode      dbms_xmldom.domnode;
 childnode     dbms_xmldom.domnode;
 myParser      dbms_xmlparser.Parser;
 rssItemsList  dbms_xmldom.domnodeList  ;
 rssItemFields dbms_xmldom.domnodeList  ;
 childNodeText varchar2(32000);
 proc        dbms_xslprocessor.processor;
 
 xsltdomdoc  dbms_xmldom.domdocument;
 xsl         dbms_xslprocessor.stylesheet;
 outdomdocf  dbms_xmldom.domdocumentfragment;
 outnode     dbms_xmldom.domnode;
 buf         varchar2(32000); 


  xsldoc     varchar2(32000):= 
   '<?xml version="1.0"?> 
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output encoding="utf-8"/>
<xsl:template match="/">
<h2>RSS Feed from AMIS Technology Weblog - most recent articles:</h2><hr />
         <ul>
         <xsl:apply-templates select="//item"/>
        </ul>
      </xsl:template>      
      <xsl:template match="//item">
<li>
         <xsl:element name="a">
           <xsl:attribute name="target"><xsl:text>_blank</xsl:text></xsl:attribute>
           <xsl:attribute name="href"><xsl:value-of select="link" /></xsl:attribute>
           <xsl:value-of select="title" />
         </xsl:element>
         <span style="font-family:sans-serif;font-size:8pt">
         <xsl:attribute name="title"><xsl:value-of select="description" /></xsl:attribute>
         <xsl:text>( Published on: </xsl:text><xsl:value-of select="pubDate" /><xsl:text>)</xsl:text></span>
       </li>
      </xsl:template>
    </xsl:stylesheet>';
 
begin
   rssFeedDoc   := dbms_xmlparser.parse(url);

   -- get XSLT stylesheet in stylesheet type variable
   myParser := dbms_xmlparser.newParser;
   dbms_xmlparser.parseBuffer(myParser, xsldoc);
   xsltdomdoc := dbms_xmlparser.getDocument(myParser);
   xsl        := dbms_xslprocessor.newstylesheet(xsltdomdoc, '');

   
   --apply stylesheet to DOM document   
   proc       := dbms_xslprocessor.newProcessor;
   outdomdocf := dbms_xslprocessor.processxsl(proc, xsl, rssFeedDoc);
   outnode    := dbms_xmldom.makenode(outdomdocf); 
   dbms_xmldom.writetobuffer(outnode, buf);
   --pl(buf);
   htp.htmlOpen;
   htp.headOpen;
   htp.title('RSS Feed - BBC Headlines');
   htp.headClose;   
   htp.BodyOpen;
   htp.p(buf);
   htp.BodyClose;
   htp.htmlClose;
end;
/

Resources

Oracle Documentation: XML Developer’s Guide