Do it yourself: an RSS reader in MicroStrategy

In the article Oracle Spaghetti - Have you read the news today?, I explained how to read a rss feed XML file from the web and use it to create an Oracle view.
Based on that view I will show here how to create MicroStrategy schema objects in order to build a RSS report.


1. Create the view:
CREATE OR REPLACE FORCE VIEW MSTR_UTIL_RSS_READER
(
   RSS_ITEM_ID,
   RSS_ITEM_TITLE,
   RSS_ITEM_LINK_URL,
   RSS_ITEM_DESCRIPTION
)
AS
   SELECT ROWNUM RSS_ITEM_ID,
          EXTRACTVALUE (VALUE (p), '/item/title') RSS_ITEM_TITLE,
          EXTRACTVALUE (VALUE (p), '/item/link') RSS_ITEM_LINK_URL,
          EXTRACTVALUE (VALUE (p), '/item/description') RSS_ITEM_DESCRIPTION
     FROM TABLE (
             XMLSEQUENCE (EXTRACT (xmltype (HTTPURITYPE.createuri ('http://www.economist.com/rss/leaders_rss.xml').getclob (),
                                            NULL,
                                            1,
                                            1),
                                   '/rss/channel/item',
                                   'xmlns:media="http://search.yahoo.com/mrss/"'))) p;

2.Open the Warehouse Catalog from the Schema menu in Desktop:

and add the view to the list of tables being used in the project, save and close.

3. Create an Attribute "RSS Item" with the following Forms:
   RSS_ITEM_ID category ID format type Number,
   RSS_ITEM_TITLE category DESC format type HTML Tag,
   RSS_ITEM_LINK_URL category (create one of your choice) format type Url,
   RSS_ITEM_DESCRIPTION category (create one of your choice) format type HTML Tag

pay attention to the Format types if you want the HTML code to be rendered properly.

4. Create a report with the Attribute "RSS Item" on rows.

I personally like to disable caching for this type of reports, so every time it's executed the data is refreshed from the Web.

You can find a working example of this report on my demo website:
http://moraschi.eu/MicroStrategy/