set echo on
set define off
set line 120

--conn rss/rss

-- Create a relational view by using XMLTable and XMLExists functions
-- along with "union all" to get a unioned set of text search results on news item titles
create or replace view newsItemsRelView AS
SELECT x1.* 
FROM newsItemsTable, XMLTable('.' passing object_value
                              Columns
					   title varchar2(120) path '/item/title',
					   uri   varchar2(160) path '/item/link',
					   description varchar2(3000) path '/item/description',
					   pubDate varchar2(80) path '/item/pubDate') x1
WHERE XMLExists('/item/title/text()[ora:contains(.,"US | U.S. | stem") > 0]' passing object_value)
UNION ALL
SELECT x2.* 
FROM newsItemsTable, XMLTable(xmlnamespaces('http://purl.org/atom/ns#' AS "a"), '.' passing object_value
                              Columns
					   title varchar2(80) path '/a:entry/a:title',
					   uri   varchar2(160) path '/a:entry/a:link/@href',
					   description varchar2(3000) path '/a:entry/a:content',
					   pubDate varchar2(40) path '/a:entry/a:issued') x2
WHERE XMLExists('declare namespace a="http://purl.org/atom/ns#"; /a:entry/a:title/text()[ora:contains(.,"US | U.S. | stem") > 0]' passing object_value)
UNION ALL
SELECT x3.* 
FROM newsItemsTable, XMLTable(xmlnamespaces('http://purl.org/rss/1.0/' AS "r1",
                                            'http://purl.org/dc/elements/1.1/' AS "dc"), '.' passing object_value
                              Columns
					   title varchar2(80) path '/r1:item/r1:title',
					   uri   varchar2(160) path '/r1:item/r1:link',
					   description varchar2(3000) path '/r1:item/r1:description',
					   pubDate varchar2(40) path '/r1:item/dc:date') x3
WHERE XMLExists('declare namespace r1="http://purl.org/rss/1.0/"; /r1:item/r1:title/text()[ora:contains(.,"Web") > 0]' passing object_value)
/

-- Create an XMLType view to generate a normalized RSS 2.0 feed
create or replace view newsItemsRSSView of xmltype
 with object id (2007) as
select XMLElement("rss", XMLAttributes('2.0' AS "version"), 
                          XMLElement("channel", 
                          XMLType('<title>An RSS Feed Generator Powered by Oracle XML DB</title>'), 
                          XMLType('<link>http://localhost</link>'), 
                          XMLType('<description>This RSS feed is generated by Oracle XML DB using binary XML storage model, XMLIndex, XQuery, SQL/XML, and many other salient capabilities</description>'),
                          XMLAgg(
                          XMLElement("item", 
                          XMLForest(title AS "title", 
                          uri AS "link", 
                          description as "description", 
                          pubDate as "pubDate")) order by title desc ))) 
                          from newsItemsRelView
/

commit
/



