set pages 80
set lines 132
set long 10000
set define off
set echo on

--conn rss/rss

--drop table newsItemsTable
--/
--commit
--/

-- Craete a binary XML table with the news item title as a virtual column
-- Notice the SECUREFILE storage option
create table newsItemsTable of XMLType  
XMLType store as SECUREFILE binary XML
(compress cache)
VIRTUAL COLUMNS
    (vc_title AS (extractValue(OBJECT_VALUE, 
                               '/item/title | /a:entry/a:title | /r1:item/r1:title',  
                               'xmlns:a="http://purl.org/atom/ns#" xmlns:r1="http://purl.org/rss/1.0/"'))
    ,vc_pubDate As (extractValue(OBJECT_VALUE, 
                               '/item/pubDate | /a:entry/a:modified | /r1:item/dc:date',  
                               'xmlns:a="http://purl.org/atom/ns#" xmlns:r1="http://purl.org/rss/1.0/" xmlns:dc="http://purl.org/dc/elements/1.1/"'))
    )
;

-- Create a unique constraint on the virtual column
ALTER TABLE newsItemsTable 
  ADD CONSTRAINT title_is_unique
  UNIQUE (vc_title);

commit
/