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

20

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:

http://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;<br />   begin<br />     rssFeedDoc   := dbms_xmlparser.parse(url);<br />&nbsp;    ...

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');<br />   itemNode:= dbms_xmldom.item(rssItemsList,0);<br />&nbsp;

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<br /> <br /> type rss_item_type is record<br /> ( title    varchar2(2000)<br /> , link     varchar2(200)<br /> , pubDate  varchar2(200)<br /> , author   varchar2(200)<br /> , category varchar2(500)<br /> , description varchar2(4000)<br /> );<br /><br /> url varchar2(500):= 'http://technology.amis.nl/blog/?feed=rss2';<br /> rssFeedDoc    dbms_xmldom.domdocument;<br /> itemNode      dbms_xmldom.domnode;<br /> childnode     dbms_xmldom.domnode;<br /> myParser      dbms_xmlparser.Parser;<br /> rssItemsList  dbms_xmldom.domnodeList  ;<br /> rssItemFields dbms_xmldom.domnodeList  ;<br /> childNodeText varchar2(32000);<br /> <br /> l_rss_item        rss_item_type;<br /> l_empty_rss_item  rss_item_type;<br /> <br /> procedure pl(text in varchar2)<br /> is<br /> begin<br />   if length(text)&gt; 255<br />   then<br />     for i in 1..(trunc(length(text)/255)+1) loop<br />       dbms_output.put_line(substr(text,1+ 255*(i-1),255));<br />     end loop;<br />   else<br />     dbms_output.put_line(text);<br />   end if;<br /> end pl;<br /><br /> procedure report_rss_item(p_rss_item in rss_item_type)<br /> is<br /> begin<br />   pl(p_rss_item.title||' by '||p_rss_item.author);<br />   pl('Published on: '||p_rss_item.pubDate);<br />   pl('Category: '||p_rss_item.category);<br />   pl('==');<br />   pl('Description: '||p_rss_item.description);<br />   pl('----------------------------------------');<br />   pl(' ');<br /> end report_rss_item;<br /><br /> <br />begin<br />   myParser := dbms_xmlparser.newParser;<br />   rssFeedDoc   := dbms_xmlparser.parse(url);<br />   rssItemsList:= dbms_xmldom.GETELEMENTSBYTAGNAME(rssFeedDoc, 'item');<br />   l_rss_item:= l_empty_rss_item;<br />   pl('RSS Feed from AMIS Technology Weblog - most recent '||dbms_xmldom.getlength(rssItemsList)||' articles:');<br />   pl('=================================================================');<br />   for i in 1..dbms_xmldom.getlength(rssItemsList) loop<br />     itemNode:= dbms_xmldom.item(rssItemsList,i-1);<br />     rssItemFields:= dbms_xmldom.getChildNodes(itemNode);<br />     for j in 1..dbms_xmldom.getlength(rssItemFields) loop<br />       childnode:= dbms_xmldom.item(rssItemFields, j-1);<br />       childNodeText:= dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(childnode));<br />       case dbms_xmldom.getNodeName(childnode)<br />       when 'title'<br />       then l_rss_item.title := childNodeText;<br />       when 'link'<br />       then l_rss_item.link := childNodeText;<br />       when 'pubDate'<br />       then l_rss_item.pubDate := childNodeText;<br />       when 'dc:creator'<br />       then l_rss_item.author := childNodeText;<br />       when 'description'<br />       then l_rss_item.description := childNodeText;<br />       when 'category'<br />       then l_rss_item.category := childNodeText;<br />       else null;<br />       end case;             <br />     end loop;<br />     report_rss_item(l_rss_item);<br />   end loop;<br />end;<br />/ <br />

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

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

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

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

&nbsp;--apply stylesheet to DOM document   <br />   proc       := dbms_xslprocessor.newProcessor;

The entire block of code:

declare<br /> <br /><br />-- url varchar2(500):= 'http://technology.amis.nl/blog/?feed=rss2';<br /> --url varchar2(500):= 'http://rss.cnn.com/rss/cnn_topstories.rss ';<br /> url varchar2(500):= 'http://newsrss.bbc.co.uk/rss/newsonline_world_edition/front_page/rss.xml ';<br />--http://www.oracle.com/technology/syndication/rss_otn_news.xml<br />--http://blogs.oracle.com/readingLists/oracleblogs.xml<br /><br /><br /> rssFeedDoc    dbms_xmldom.domdocument;<br /> itemNode      dbms_xmldom.domnode;<br /> childnode     dbms_xmldom.domnode;<br /> myParser      dbms_xmlparser.Parser;<br /> rssItemsList  dbms_xmldom.domnodeList  ;<br /> rssItemFields dbms_xmldom.domnodeList  ;<br /> childNodeText varchar2(32000);<br /> proc        dbms_xslprocessor.processor;<br /> <br /> xsltdomdoc  dbms_xmldom.domdocument;<br /> xsl         dbms_xslprocessor.stylesheet;<br /> outdomdocf  dbms_xmldom.domdocumentfragment;<br /> outnode     dbms_xmldom.domnode;<br /> buf         varchar2(32000); <br /> <br />  xsldoc     varchar2(32000):= <br />   '&lt;?xml version=&quot;1.0&quot;?&gt; <br />    &lt;xsl:stylesheet version=&quot;1.0&quot; xmlns:xsl=&quot;http://www.w3.org/1999/XSL/Transform&quot;&gt;<br />      &lt;xsl:output encoding=&quot;utf-8&quot;/&gt;<br />&lt;xsl:template match=&quot;/&quot;&gt;<br />         &lt;rssItems&gt;<br />         &lt;xsl:apply-templates select=&quot;//item&quot;/&gt;<br />        &lt;/rssItems&gt;<br />      &lt;/xsl:template&gt;      <br />      &lt;xsl:template match=&quot;//item&quot;&gt;<br />         &lt;rssItem&gt;<br />           &lt;xsl:value-of select=&quot;title&quot;/&gt;&lt;xsl:text&gt;<br />           by: &lt;/xsl:text&gt;<br />           &lt;xsl:value-of select=&quot;creator&quot;/&gt;&lt;xsl:text&gt;<br />           Published on: &lt;/xsl:text&gt;&lt;xsl:value-of select=&quot;pubDate&quot;/&gt;<br />           &lt;xsl:text&gt;<br />           Category: &lt;/xsl:text&gt;&lt;xsl:value-of select=&quot;category&quot;/&gt;<br />           &lt;xsl:text&gt;<br />           ==<br />           &lt;/xsl:text&gt;&lt;xsl:value-of select=&quot;description&quot;/&gt;&lt;xsl:text&gt;<br />           -----------------------------------------------------------------------<br />           &lt;/xsl:text&gt;<br />           &lt;/rssItem&gt;<br />      &lt;/xsl:template&gt;<br />    &lt;/xsl:stylesheet&gt;';<br /> <br /> procedure pl(text in varchar2)<br /> is<br /> begin<br />   if length(text)&gt; 255<br />   then<br />     for i in 1..(trunc(length(text)/255)+1) loop<br />       dbms_output.put_line(substr(text,1+ 255*(i-1),255));<br />     end loop;<br />   else<br />     dbms_output.put_line(text);<br />   end if;<br /> end pl;<br /><br /> <br /> <br />begin<br />   rssFeedDoc   := dbms_xmlparser.parse(url);<br /><br />   -- get XSLT stylesheet in stylesheet type variable<br />   myParser := dbms_xmlparser.newParser;<br />   dbms_xmlparser.parseBuffer(myParser, xsldoc);<br />   xsltdomdoc := dbms_xmlparser.getDocument(myParser);<br />   xsl        := dbms_xslprocessor.newstylesheet(xsltdomdoc, '');<br /><br />   <br />   --apply stylesheet to DOM document   <br />   proc       := dbms_xslprocessor.newProcessor;<br />   outdomdocf := dbms_xslprocessor.processxsl(proc, xsl, rssFeedDoc);<br />   outnode    := dbms_xmldom.makenode(outdomdocf); <br />   dbms_xmldom.writetobuffer(outnode, buf);<br />   pl('RSS Feed from AMIS Technology Weblog - most recent articles:');<br />   pl('=================================================================');<br />   pl(buf);<br />end;<br />/ <br />

The results: 

RSS Feed from AMIS Technology Weblog - most recent articles:<br />=================================================================<br />&lt;rssItems&gt;<br />  &lt;rssItem&gt;Building an RSS Feed Reader in PL/SQL - using<br />dbms_xmlparser, dbms_xmldom and dbms_xslprocessor for parsing and transforming<br /><br />by: <br />           Published on: Sat, 29 Apr 2006 18:22:53 +0000<br /><br />Category: General<br /><br />==<br />           <br />This article will demonstrate how we can build an RSS Feed Reader<br />in PL/SQL. We will use DBMS_XMLPARSER to read an RSS feed from an URL and<br />parsethe RSS document, then using the DBMS_XMLDOM package we will analyze the<br />document and then<br />make use of the DBMS_XSLPROCESSOR package to transform the RSS document into a<br />[...]<br /><br />-----------------------------------------------------------------------<br /><br />&lt;/rssItem&gt;<br />  &lt;rssItem&gt;Getting used to Oracle SQL Developer<br />           by: <br /><br />Published on: Fri, 28 Apr 2006 15:30:34 +0000<br />           Category: Devel. +<br />PL/SQL tools<br />           ==<br />           <br />&amp;amp;#160;It took a few months, but I<br />finally start appreciating the Oracle<br />SQL Developer developed by Oracle (The<br />Project Former<br />ly Known As Raptor).<br />There are still some bugs, for instance with debugging<br />through a<br />firewall (hey, didn&amp;amp;#8217;t we have the same problem with<br />JDeveloper?) and<br />some features are missing, but it&amp;amp;#8217;s quite usable. <br />

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:

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

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<br />procedure rss_reader<br />as <br /><br /> url varchar2(500):= 'http://newsrss.bbc.co.uk/rss/newsonline_world_edition/front_page/rss.xml ';<br /><br /> rssFeedDoc    dbms_xmldom.domdocument;<br /> itemNode      dbms_xmldom.domnode;<br /> childnode     dbms_xmldom.domnode;<br /> myParser      dbms_xmlparser.Parser;<br /> rssItemsList  dbms_xmldom.domnodeList  ;<br /> rssItemFields dbms_xmldom.domnodeList  ;<br /> childNodeText varchar2(32000);<br /> proc        dbms_xslprocessor.processor;<br /> <br /> xsltdomdoc  dbms_xmldom.domdocument;<br /> xsl         dbms_xslprocessor.stylesheet;<br /> outdomdocf  dbms_xmldom.domdocumentfragment;<br /> outnode     dbms_xmldom.domnode;<br /> buf         varchar2(32000); <br />
<br />  xsldoc     varchar2(32000):= <br />   '&lt;?xml version=&quot;1.0&quot;?&gt; <br />    &lt;xsl:stylesheet version=&quot;1.0&quot; xmlns:xsl=&quot;http://www.w3.org/1999/XSL/Transform&quot;&gt;<br />      &lt;xsl:output encoding=&quot;utf-8&quot;/&gt;<br />&lt;xsl:template match=&quot;/&quot;&gt;<br />&lt;h2&gt;RSS Feed from AMIS Technology Weblog - most recent articles:&lt;/h2&gt;&lt;hr /&gt;<br />         &lt;ul&gt;<br />         &lt;xsl:apply-templates select=&quot;//item&quot;/&gt;<br />        &lt;/ul&gt;<br />      &lt;/xsl:template&gt;      <br />      &lt;xsl:template match=&quot;//item&quot;&gt;<br />&lt;li&gt;<br />         &lt;xsl:element name=&quot;a&quot;&gt;<br />           &lt;xsl:attribute name=&quot;target&quot;&gt;&lt;xsl:text&gt;_blank&lt;/xsl:text&gt;&lt;/xsl:attribute&gt;<br />           &lt;xsl:attribute name=&quot;href&quot;&gt;&lt;xsl:value-of select=&quot;link&quot; /&gt;&lt;/xsl:attribute&gt;<br />           &lt;xsl:value-of select=&quot;title&quot; /&gt;<br />         &lt;/xsl:element&gt;<br />         &lt;span style=&quot;font-family:sans-serif;font-size:8pt&quot;&gt;<br />         &lt;xsl:attribute name=&quot;title&quot;&gt;&lt;xsl:value-of select=&quot;description&quot; /&gt;&lt;/xsl:attribute&gt;<br />         &lt;xsl:text&gt;( Published on: &lt;/xsl:text&gt;&lt;xsl:value-of select=&quot;pubDate&quot; /&gt;&lt;xsl:text&gt;)&lt;/xsl:text&gt;&lt;/span&gt;<br />       &lt;/li&gt;<br />      &lt;/xsl:template&gt;<br />    &lt;/xsl:stylesheet&gt;';<br /> <br />begin<br />   rssFeedDoc   := dbms_xmlparser.parse(url);<br /><br />   -- get XSLT stylesheet in stylesheet type variable<br />   myParser := dbms_xmlparser.newParser;<br />   dbms_xmlparser.parseBuffer(myParser, xsldoc);<br />   xsltdomdoc := dbms_xmlparser.getDocument(myParser);<br />   xsl        := dbms_xslprocessor.newstylesheet(xsltdomdoc, '');<br /><br />   <br />   --apply stylesheet to DOM document   <br />   proc       := dbms_xslprocessor.newProcessor;<br />   outdomdocf := dbms_xslprocessor.processxsl(proc, xsl, rssFeedDoc);<br />   outnode    := dbms_xmldom.makenode(outdomdocf); <br />   dbms_xmldom.writetobuffer(outnode, buf);<br />   --pl(buf);<br />   htp.htmlOpen;<br />   htp.headOpen;<br />   htp.title('RSS Feed - BBC Headlines');<br />   htp.headClose;   <br />   htp.BodyOpen;<br />   htp.p(buf);<br />   htp.BodyClose;<br />   htp.htmlClose;<br />end;<br />/<br />

Resources

Oracle Documentation: XML Developer’s Guide

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

20 Comments

  1. I don’t know what is wrong with my installation, but it does not seem to want to work. I will run it again and post the error message here if that’s allowed

  2. Hello,

    I have a question regarding on how to restrict the number of rss feeds to be listed in the page?
    Is there any way I could pass some kind of number, so that I would be able to display only first 5 rss feeds.

    Any help is highly appreciable.

    Thanks

  3. Hello,

    I am trying to use this example for developing a RSS reader. When I change the URL for the rss feed, I am getting the following error:

    Value too large for the buffer.

    How can I resolve this? I changed the data type from VARCHAR2 to LONG.

    Thanks

  4. These are great solutions to the problem of grabbing XML from a remote RSS feed.

    I’m curious what woudl be the best way to simply return the RSS feed as a plain XML document so I could parse it later through another tier (such as an RIA, like Flex)?

    Would I just want to use UTL_HTTP?

    Also, does anyone have experience providing search parameters to the select * from xmltable using XQuery? So if I wanted to return only results from the feed where description contained a certain word?

    Thanks.

  5. This code works great for a small feed app. I’ve been building, however I’m getting a strange parse error when I try to run this code over an non-rss xml document. Seems like it should work ….

    I’m working in 10gR2. Here’s the xml document:

    ——————————————————————————————

    ]>

    1. A retirement plan that allows you a charitable deduction is
    A. an IRA.
    B. a 401(k) plan.
    C. a unitrust.
    2. A portion of the sum paid each year from a charitable gift annuity is
    A. tax-free.
    B. appreciated property.
    C. deferred.
    3. A pooled income fund
    A. holds liquid assets only.
    B. must be invested in short-term securities.
    C. distributes its earnings to a number of beneficiaries.
    4. To get a current tax deduction, your life income gift must be
    A. revocable.
    B. amendable.
    C. irrevocable.
    5. A charitable remainder annuity trust pays the beneficiary
    A. a percentage of market value.
    B. a fixed dollar amount.
    C. its net income.
    6. The greater the annual payout of a charitable remainder trust,
    A. the greater the deduction.
    B. the smaller the deduction.
    C. the higher the capital gain.
    7. You can get a current income tax charitable deduction for your home if you
    A. deed it to a charitable organization.
    B. will it to the IRS.
    C. move out now.
    8. Your most important reward from a life income gift is
    A. a tax deduction.
    B. increasing your income.
    C. the joy of helping others.

    Now, check your answers.
    ]]>

    ———————————————————————————————-
    When I call the code from a stored procedure (to a web page) I get the following error:

    PACKAGE: RSS.story SQLCODE: ORA-31011: XML parsing failed ORA-19202: Error occured in XML processingLPX-00111: Warning: element “ARTICLE” attribute “CATEGORIES” has invalid enumeration value “QUIZZES” Error at line 17

    Any clues would be greatly appreciated …
    JLW

  6. mmm the WordPress Blog doesn’t like my XSLT, the previous should be

    And if namespaces are causing any troubles, you can always remove them from the XML.

    The following works on 9.2, 10.1 and 10.2

    select value( i ).getstringval() creator
    from table( XMLSequence( HTTPURITYPE( ‘http://technology.amis.nl/blog?feed=rss2′ ).getXML().transform
    ( xmltype( ‘<?xml version=”1.0″?>
    <xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”>
    <xsl:template match=”*”>
    <xsl:element name=”{local-name()}”>
    <xsl:apply-templates select=”node()”/>
    </xsl:element>
    </xsl:template>
    </xsl:stylesheet>’ )
    ).extract(‘//item’) ) ) i

    Note that the XSLT transformation removes not only the namespaces, but also the attributes. If you need them you have tou use something like

    <?xml version=”1.0″?>
    <xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”>
    <xsl:template match=”*”>
    <xsl:element name=”{local-name()}”>
    <xsl:apply-templates select=”@*|node()”/>
    </xsl:element>
    </xsl:template>
    <xsl:template match=”@*”>
    <xsl:attribute name=”{local-name()}”>
    <xsl:value-of select=”.”/>
    </xsl:attribute>
    </xsl:template>
    </xsl:stylesheet>

  7. And if namespaces are causing any troubles, you can always remove them from the XML.

    The following works on 9.2, 10.1 and 10.2

    select value( i ).extract( ‘/item/creator/text()’ ).getstringval() creator
    from table( XMLSequence( HTTPURITYPE( ‘http://technology.amis.nl/blog?feed=rss2′ ).getXML().transform
    ( xmltype( ‘

    ‘ )
    ).extract(‘//item’) ) ) i

    Note that the XSLT transformation removes not only the namespaces, but also the attributes. If you need them you have tou use something like

  8. Marco Gralike on

    …OR in the new syntax with XMLTABLE (can’t find a 10gR1 database so maybe it won’t work on that version)

    [code]

    select *
    from xmltable('//item'
    passing HTTPURITYPE('http://technology.amis.nl/blog?feed=rss2').getXML()
    columns
    title varchar2(4000) path '/item/title/text()',
    link varchar2(4000) path '/item/link/text()',
    publication_date varchar2(4000) path '/item/pubDate/text()',
    creator varchar2(4000) path '/item/*[namespace-uri()="http://purl.org/dc/elements/1.1/" and local-name()="creator"]/text()',
    description varchar2(4000) path '/item/description/text()'
    -- category varchar2(4000) path '/item/category/text()'
    )
    ;

    [/code]

    This syntax doesn’t like the multiple categories values. Haven’t found (yet) a workaround (but because its XQuery related stuff – there should be one)

    M.

  9. Handling namespaces can be done in another way:

    select value( i ).extract( ‘/item/dc:creator/text()’, ‘xmlns:dc=http://purl.org/dc/elements/1.1/”‘ ).getstringval() creator
    from table( XMLSequence( HTTPURITYPE( ‘http://technology.amis.nl/blog?feed=rss2′ ).getXML().extract(‘//item’) ) ) i

    And the difference between 10.1 and 10.2

    On 10.1 the extract(‘//items’) returns
    [dc:creator]Bert Jan Meinders[/dc:creator]

    i.e. without the namespace information

    On 10.2 the namespace url is added
    [dc:creator xmlns:dc="http://purl.org/dc/elements/1.1/"]Bert Jan Meinders[/dc:creator]

  10. So apparently I am somewhat old fashioned (10gR1) as Harm pointed out. Anton’s very neat trick which did not even require the use of PL/SQL will be available to me when I finally upgrade to 10gR2. I am most impressed by the way with Anton’s handling of the dc:author element – namespaces can be horrible when parsing and transforming!

    It is interesting to see how many ways there are to work with XML content in the Oracle database. It is also good to have colleagues like Harm and Anton who can show me the (proper) way.

  11. Mijn code werkt op 10.2, meer geeft op 10.1 de volgende fout
    ERROR at line 2:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00234: namespace prefix “dc” is not declared
    Error at line 6
    ORA-06512: at “SYS.XMLTYPE”, line 105
    ORA-06512: at line 1

    De code van Lucas werkt op 10.1, maar geeft op 10.2 de volgende fout
    ERROR at line 1:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00216: invalid character 128 (0x80)
    Error at line 145
    ORA-06512: at “XDB.DBMS_XMLDOM”, line 4145
    ORA-06512: at “XDB.DBMS_XMLDOM”, line 4170
    ORA-06512: at line 45

  12. Did you ever tried something like:

    select
    value( i ).extract( ‘/item/title/text()’ ).getstringval() title
    , value( i ).extract( ‘/item/link/text()’ ).getstringval() link
    , value( i ).extract( ‘/item/pubDate/text()’ ).getstringval()
    , value( i ).extract( ‘/item/*[namespace-uri()="http://purl.org/dc/elements/1.1/" and local-name()="creator"]/text()’ ).getstringval() creator
    , value( i ).extract( ‘/item/description/text()’ ).getclobval() description
    , value( i ).extract( ‘/item/category/text()’ ).getstringval() category
    from table( XMLSequence( HTTPURITYPE( ‘http://technology.amis.nl/blog?feed=rss2′ ).getXML().extract(‘//item’) ) ) i

    Anton

  13. Harm Verschuren on

    About compactness: my solution will use less CPU. dbms_xmlparser, dbms_xmltransform and dbms_xmldom under the covers use java (CPU intensive and context-switch between PL/SQL – java); xmltype and dbms_genxml don’t. So the latter will have less impact on the performance (important when data volumes increase).
    Xmltype parses automatically under the covers upon cretion and also has a transform method. Try to use that one instead of dbms_xmltransform.

  14. What I found on the internet suggestes no direct transformation. Here is some code:

    declare
    xml xmltype;
    childClob clob;
    doc dbms_xmldom.domdocument;
    root_element dbms_xmldom.domelement;
    begin
    … — fetch data into xml
    — Turn XMLType into DomDocument
    doc := dbms_xmldom.newdomdocument(xml);
    — do some processing on DomDocument
    root_element := dbms_xmldom.getdocumentelement(doc);
    root_node := dbms_xmldom.makenode(root_element);
    — turn Node from DomDocument into XMLType
    dbms_xmldom.writetoclob(root_node, childClob);
    xml := xmltype(child_clob);

    end;

    You would hope it could go more straightforward from DomDocument to XMLType, benefiting from the fact that it is already an XML tree structure.

  15. Thanks Harm, that is pretty straightforward.

    I am not sure it is more compact than my original
    rssFeedDoc := dbms_xmlparser.parse(url);
    but I can see how the methods available on XMLType will make further processing easier than through the dbms_xmldom package.

    Do you happen to know whether there is an easy way of converting a dbms_xmldom.domdocument variable in an XMLType variable?

  16. Harm Verschuren on

    Oh, and in case you have trouble with the ampersand in the url, issue the following in sqlplus: SET DEFINE OFF

  17. Harm Verschuren on

    Here’s a simple function that you could use. It returns a parsed RSS feed, based on the URL (and optional proxy address in case you are behind a proxyserver).

    Usage: select get_rss(‘http://technology.amis.nl/blog/?feed=rss2&p=1173′, null) from dual;

    create or replace function get_rss(p_url varchar2, p_proxy varchar2 default null)
    return sys.XMLType
    is

    — Author: Harm Verschuren, 01-05-2006

    t_pieces utl_http.html_pieces;
    t_idx pls_integer;
    t_payload clob := empty_clob();
    begin
    if p_proxy is null
    then
    t_pieces := utl_http.request_pieces(p_url);
    else
    t_pieces := utl_http.request_pieces(url => p_url, proxy => p_proxy);
    end if;
    for t_idx in t_pieces.first..t_pieces.last
    loop
    t_payload := t_payload || t_pieces(t_idx);
    end loop;
    return xmltype(t_payload);
    end;

  18. Is there an easy way (in 10gR1) to create an XMLType variable based on a document found at an URL somewhere on the internet (like I have done with dbms_xmlparser.parse(url)? Or would I use utl_http to get the data in a CLOB and then construct the XMLType using XMLType.createXML( p_clob)?

    On dbms_output: I am using a 10g1R1 database so for me that limit still exists (it was removed only in 10gR2).