Written by: ilmarkerm

Category: Blog entry

Published: May 20, 2010

A small SQL code sample this time, how to read RSS file in SQL. I’m using it to display and aggregate some RSS feeds in APEX application.
All columns are directly read from XML (plus some additinal code to get the time zone correct), except pubdate_local that is pubdate converted to date datatype in local time zone. The first example is reading from a local file localnews.rss under directory DIR1.

SELECT title,
       link,
       description,
       author,
       pubdate,
       CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
  FROM (    SELECT title,
                   link,
                   description,
                   author,
                   TO_TIMESTAMP_TZ (
                      REPLACE(pubdate, 'PDT', 'PST PDT'),
                      CASE
                         WHEN REGEXP_LIKE (pubdate, '[[:digit:]]{4}$')
                         THEN
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZHTZM'
                         ELSE
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZR TZD'
                      END,
                      'NLS_DATE_LANGUAGE = American')
                      pubdate
              FROM XMLTABLE (
                      '/rss/channel/item'
                      PASSING xmltype (BFILENAME ('DIR1', 'localnews.rss'),
                                       NLS_CHARSET_ID ('AL32UTF8'))
                      COLUMNS title VARCHAR2 (250 CHAR) PATH 'title',
                              link VARCHAR2 (250 CHAR) PATH 'link',
                              author VARCHAR2 (250 CHAR) PATH 'author',
                              description CLOB PATH 'description',
                              pubdate VARCHAR2 (50) PATH 'pubDate'));

And the same SQL when reading RSS directly from internet over HTTP (BFile is replaced with HttpUriType).

SELECT title,
       link,
       description,
       author,
       pubdate,
       CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
  FROM (    SELECT title,
                   link,
                   description,
                   author,
                   TO_TIMESTAMP_TZ (
                      REPLACE(pubdate, 'PDT', 'PST PDT'),
                      CASE
                         WHEN REGEXP_LIKE (pubdate, '[[:digit:]]{4}$')
                         THEN
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZHTZM'
                         ELSE
                            'Dy, dd fmMonth YYYY HH24:MI:SS TZR TZD'
                      END,
                      'NLS_DATE_LANGUAGE = American')
                      pubdate
              FROM XMLTABLE (
                      '/rss/channel/item'
                      PASSING HttpUriType('http://feeds.feedburner.com/DougsOracleBlog').getXML()
                      COLUMNS title VARCHAR2 (250 CHAR) PATH 'title',
                              link VARCHAR2 (250 CHAR) PATH 'link',
                              author VARCHAR2 (250 CHAR) PATH 'author',
                              description CLOB PATH 'description',
                              pubdate VARCHAR2 (50) PATH 'pubDate'));

Thanks to Daniel Morgans session today at EMEA Harmony, I now have a personal goal to get the Oracle ACE title 🙂 Need to get working towards it and become more active in the community.