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!

....

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

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:

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

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

20 Comments

  1. Helen Hunt January 1, 2010
  2. Pon September 2, 2008
  3. Pon August 12, 2008
  4. Chris March 26, 2008
  5. JLW June 15, 2006
  6. anton May 4, 2006
  7. anton May 4, 2006
  8. Marco Gralike May 1, 2006
  9. anton May 1, 2006
  10. Lucas Jellema May 1, 2006
  11. anton May 1, 2006
  12. anton May 1, 2006
  13. Harm Verschuren May 1, 2006
  14. Lucas Jellema May 1, 2006
  15. Lucas Jellema May 1, 2006
  16. Lucas Jellema May 1, 2006
  17. Harm Verschuren May 1, 2006
  18. Harm Verschuren May 1, 2006
  19. Lucas Jellema April 30, 2006
  20. Aino Andriessen April 30, 2006