Oracle BI Publisher Blog

Collaborate06 has started with a bang ... this hotel is huge, just to give you an idea how big ... they have a river running through the grounds of the hotel (all of which are under glass) and you can ride a boat and get a half hour guided tour of the hotel. The fish are huge, wish I had brought my rod!

Monday is pretty much given over to Special Interest Groups during the day. This evening the exhibition floor opened to floods of attendees with burning questions. We have an Applications Technology pod staffed by yours truly and a few other brave souls. It was pretty busy ... not OpenWorld busy but busy enough.

Adding External Data

I got a request tonight; 'How can I bring other data into the layout that is not in the datasource provided?' ... there are a couple of ways to do this. Now Im not going to tell you how to execute a query from the template to pull in more data from the db ... that starts to tie the template to the data extract too closely and we go down a path we do not want to go ... that said its possible but we're not recommending it. If you have some static data or can call a URL to pull XML data; maybe a servlet or static XML document then you can easily integrate this into you output.

There is a function in XSL called document(), we can use this to pull XML data into the template very easily. Basically we declare the function at the top of the template. For this example lets use a well known RSS news feed.

<xsl:variable name="news" select="document('http://rss.news.yahoo.com/rss/topstories')"/>

We create a variable, 'news'  and assign the RSS feed results to it using the document function. The variable will contain a nodeset or tree of data not just a single element.

<?xml version="1.0" encoding="iso-8859-1" ?>
 <rss version="2.0" xmlns:media="http://search.yahoo.com/mrss">
 <channel>
 <title>Yahoo! News: Top Stories</title>
 <copyright>Copyright (c) 2006 Yahoo! Inc. All rights reserved.</copyright>
 <link>http://news.yahoo.com/i/716</link>
 <description>Top Stories</description>
 <language>en-us</language>
 <lastBuildDate>Tue, 25 Apr 2006 06:18:26 GMT</lastBuildDate>
 <ttl>5</ttl> 
 <image> <title>Yahoo! News</title>
 ...

We can now reference the "news" variable and its contents in the template.

The image shows a portion of a template using the "news" variable.

FE - <?for-each:$news/*?> - this is pulling the complete nodeset into the for-each loop
Channel Link - <?/rss/channel/title?> simply referencing the title element
Link - this is a piece of text i.e. 'Link' with an MSWord hyperlink layered over the top,{/rss/channel/link}  - I'll cover the whole notion of links and dynamic links in another post.
FE - <?for-each:item?> - now we start looping over the news item data just as we would normally.
The rest you can check out yourselves, the template is posted here. When you test remember you may need to set a proxy to get out of your firewall.

So pretty simple huh? Of course you can get a lot more imaginative, say your XML source accepted parameters you could pass parameter values based on the main data set, you can build dynamic URLs to open another report, even a self service web page or an Oracle Form or anything else for that matter.
Busy day tomorrow with panels, presentations and the demogrounds ... see ya!