Combine 300 RSS feeds into a MySQL table

Questions
, ,
1

I want to extract the data from 300 RSS feeds and combine them into a MySQL tabel. It concerns a few thousand of entries every day.

A sample of such a feed is KINOTIP2.cz.

I have looked at the RSS connector, but use of that would require 300 data containers to be defined which does not scale well.

Is there an alternative?

2

The easiest way to handle this scenario is probably to first define a table with the URLs of the RSS feeds. You can define a data container on MySQL using settings*.xml, but for simplicity I will create a table in-memory:

    create or replace table xmlfeeds@inmemorystorage
    as
    select 'https://www.kinotip2.cz/rss/seznam.html' url
    union all
    select 'https://www.nu.nl/rss/Algemeen' url
    union all
    select 'https://www.bnr.nl/podcast/digitaal?widget=rssfeed'

In the next step, you can retrieve all the RSS-feeds using httpdownload and then parse the XML contents using xmltable as shown below:

    create or replace table entries2load@inmemorystorage
    as
    select xfd.url
           label 'Feed (URL)'
    ,      ety.item_title
    ,      ety.item_description
    ,      ety.item_pub_date
    ,      ety.channel_title
    from   XMLFEEDS@InMemoryStorage xfd
    join   HTTPDOWNLOAD@DataDictionary(xfd.url) htp
    join   xmltable
           ( '/rss/channel/item'
             passing htp.content_clob
             columns item_link               varchar2 path 'link'
             ,       item_guid               varchar2 path 'guid'
             ,       item_guid_is_perma_link bool path 'guid/@isPermaLink'
             ,       item_title              varchar2 path 'title'
             ,       item_pub_date           datetime path 'pubDate'
             ,       item_description        varchar2 path 'description'
             ,       item_category           varchar2 path 'category'
             ,       channel_title           varchar2 path '../title'
             ,       channel_link            varchar2 path '../link'
             ,       channel_description     varchar2 path '../description'
             ,       channel_language        varchar2 path '../language'
             ,       channel_web_master      varchar2 path '../webMaster'
             ,       channel_copyright       varchar2 path '../copyright'
             ,       channel_last_build_date date path '../lastBuildDate'
           )
           ety

The results will resemble:

image
image935×459 42.5 KB

The final step is to bulk load the data into your MySQL database. When there is yet no existing table, you might want to use create or replace table NAME@mysql instead of bulk insert. Code to load the RSS entries from all sources into MySQL table:

    bulk insert into NAME@mysql
    ( item_link
    , ...
    )
    select item_link
    ,      ...
    from   entries2load@inmemorystorage

For incremental loading you might want to consider the use of the synchronize statement which you can configure to only update existing rows and insert new rows. Another forum topic describes the synchronization between two database tables across data containers. More details on synchronize are in the Invantive SQL grammar and some other samples: concept and sample Visma.net.