As Published In
Oracle Magazine
January/February 2005

TECHNOLOGY: XML Exchange


Share Your Data as XML

By Sean Dillon

Make existing relational and object-based data available as XML.

XML has quickly become the de facto mechanism for sharing data between disparate information systems. IT departments are deciding between purchasing native XML databases and converting existing data from relational and object-based storage to an XML model that can be shared with business partners. In this column, I discuss how to use Oracle Database 10g to leverage your existing relational or object-based data and make it available as XML, in any format you see fit.

History 101

Adopting new data formats or accommodating new mechanisms for storage is not something new to Oracle. In the early 1990s, object-oriented (OO) programmers decided that the impedance mismatch between an object with its data attributes and a relational table with columns was simply too complex for development teams to deal with. Thus began what I like to call the Object Revolution , in which object-oriented database companies began competing with the big relational database management system (RDBMS) vendors.

Oracle recognized the need to support object technology in its database, so it launched several initiatives to make this a reality. Oracle introduced a Java virtual machine inside the database kernel; a new database construct known as an Object Type or User-Defined Type (UDT); and probably most important, the object view. Object views gave DBAs and programmers the ability to create a database view of a UDT, where the result set is a collection of objects instead of a set of relational rows.

This object view concept was critical, because it gave Oracle shops the ability to leverage their existing applications, reporting facilities, and storage.

History Repeats

Objects are still a large part of Oracle's strategy, and they're fully supported in Oracle Database 10g. Today, however, the industry is far less focused on object support (largely because it's already done). In the same way that the industry asked for object support from the database in the 1990s, the industry is now asking the database for XML capabilities.

In upcoming issues of Oracle Magazine , this column will demonstrate how Oracle Database has been enhanced to support XML capabilities such as the new XML datatype, XML programming support in parsers and XSL transformations, XML Schema support, and XML storage options. This column, however, concentrates on one aspect of the new database functionality: XML generation. During the Object Revolution, we taught the database how to make relational data available as user-defined types. Today, the database can make data available as XML. Let's look at a practical example of how to serve up our existing relational and object-based data as XML.

Real Simple Syndication

Many popular Web sites are now supporting an RSS news feed (RSS stands for RDF Site Summary, Rich Site Summary , or Real Simple Syndication , depending on whom you ask). RSS is a lightweight, extensible XML format for performing data syndication.

For Web sites that employ this technology, the data being syndicated through RSS is typically the same content that's available on the Web site; it's just offered in an XML format. In nearly all cases, the data originates from relational or object-based data that is used in publishing the existing Web site. When the news is published as XML, tools known as RSS readers are able to read these news feeds and aggregate them into a consolidated news listing.

Let's take a look at how we can take an existing data model, create an XML view of it, and make that view available on the Web as an RSS news feed. This is a great example, because the underlying data model can be very simple. The XML format isn't terribly complex, and knowing how to publish RSS from your Oracle database is a great first step in understanding the XML generation possibilities in Oracle Database 10g.

Let's look at the data model we'll use to publish the news feed. To keep it simple, we use some tables from the sample schemas included in Oracle9i Database and Oracle Database 10g. Specifically, we'll use the EMPLOYEES and DEPARTMENTS tables from the hr sample schema.

Using these simple tables, we create an RSS feed that tells a manager the five employees most recently hired for the department. This query is very simple. In this case, we ask for the five employees most recently hired for the Sales department (department ID: 80): 

select e.first_name, e.last_name, 
      e.hire_date
  from (select first_name, 
              last_name, 
              hire_date, 
              department_id  
          from employees
        order by hire_date desc) e
 where department_id = 80
   and rownum < 6
/


We use a healthy mix of SQL/XML, PL/SQL, and DBUri (with the DBUri servlet) to get this report published in an RSS format.

First, we create a PL/SQL function that takes a department ID and returns an XMLType object formatted as an RSS document, as shown in Listing 1.

Code Listing 1: PL/SQL function that returns an XMLType object as an RSS document 

create or replace function get_department_xml (p_department_id in number)
  return xmltype
as
  -- This will be used to populate and return the XML document
  l_xml xmltype;
begin
  -- Use a SQL/XML query to generate the RSS document and select it
  -- into the l_xml local variable.
  select xmlelement( "rss",
           xmlattributes( '0.91' as "version" ),
           xmlelement( "channel",
             xmlforest( 'Department New Hire Report' as "title",
                        'http://oramag.oracle.com' as "link",
                        p_department_id "description",
                        'en-us' as "language",
                        'Copyright 1999-2004, Oracle Corporation' 
                          as "copyright"
             ),
             xmlelement("image",
               xmlforest('Most Recently Hired Employees' as "title",
                         'http://asktom.oracle.com/i/asktom2.gif' 
                           as "url",
                         'http://oramag.oracle.com' "link",
                         '50' as "width",
                         '50' as "height",
                         'A sample RSS document showing new hires.'
                         as "description")
             ),
             ( select xmlagg(
                        xmlelement( "item",
                          xmlforest(x.first_name||' '|| 
                            x.last_name||', '||x.hire_date as "title",
                            '/oradb/HR/EMPLOYEES/ROW[EMPLOYEE_ID=' ||
                              x.employee_id || ']' as "link",
                            x.hire_date as "description" ) ) )
                 from ( select e.first_name, e.last_name, e.hire_date, 
                               e.employee_id, d.department_name
                          from employees e, departments d
                         where d.department_id = p_department_id
                           and d.department_id = e.department_id
                         order by e.hire_date desc) x
                where rownum < 6 ) ) )
    into l_xml
    from dual;
  --
  return l_xml;
end get_department_xml;


Note that the functions we see in Listing 1— XMLElement(), XMLAttributes(), XMLForest() , and XMLAgg() —are all part of SQL/XML, an emerging industry standard that allows SQL queries to return XML documents.

The XMLElement() function creates an XML element in the document. Parameters passed to this function determine if the element will contain attributes or nested XML markup. The XMLAttributes() function creates one or more attributes embedded in a particular element, and the XMLForest() function creates one or more optional elements. Finally, the XMLAgg() function aggregates numerous rows into a collection of XML nodes. These SQL/XML functions give us all the flexibility we need for creating deeply nested XML documents with unlimited flexibility.

The next step is to create the XMLType view that returns an RSS document for each DEPARTMENT_ID in the DEPARTMENTS table. We query the DEPARTMENTS table for the DEPARTMENT_ID , which is then passed into the GET_DEPARTMENT_XML function, resulting in a view returning an RSS document for each row in the DEPARTMENTS table:

Next Steps


READ
 XML

RDF/RSS
 www.w3.org/TR/REC-rdf-syntax/
 www.xml.com/pub/a/2002/12/18/dive-into-xml.html

 DOWNLOAD Firefox and Sage

 

create or replace view 
dept_newhires
of xmltype with object id (
  extract(object_value,   
  '/rss/channel/description/text()')
  .getnumberval()) as
  select get_department_xml(
         department_id)
    from departments
/


In this SQL statement, we create an XMLType view named DEPT_NEWHIRES . Every XMLType view must have a unique Object ID that serves as a unique identifier for the row of the view. We create the unique Object ID by extracting the DEPARTMENT_ID , using an XPath expression ('/rss/channel/description/text()') from the OBJECT_VALUE. OBJECT_VALUE is a pseudocolumn that is created when the view is queried. Now that the view is created, all we need to do is call it up, using a Web browser.

To get this data from a SQL view to the Web, we use a database feature known as the DBUri Servlet. It enables you to view the contents of tables or views in an Oracle database over the Web. Our DBUri request is formatted as follows: 

http://hr:hr@asktom.oracle
.com:8080/oradb/HR/DEPT_NEWHIRES/ROW/rss[channel/description=80] 


We use four primary elements to call this DBUri Servlet:

  • Username and password. Because we need to log in to the database, we pass the username and password in the URL line. This lets us call the URL from an RSS reader as well, without having to manually log in every time the reader refreshes the data. We use the HR user with the HR password ( hr:hr ).

  • Hostname and port. This is the hostname and port the Oracle database listener is running on. In our case, we use the asktom.oracle.com server on port 8080.

  • DBUri specification. This comes in the format /oradb/USERNAME/TABLE or VIEW . /oradb/ tells the listener that the request is for the DBUri Servlet, and the USERNAME and TABLE or VIEW tell the Servlet what data to get. We use the DEPT_NEWHIRES view in the HR schema.

  • Constraining expression. We also pass a constraining expression to tell the DBUri Servlet how to constrain the query it issues. This expression is an XPath expression that not only specifies the root element to select from the resulting XML but can also constrain which XML document is returned. We use the XPath expression /DEPT_NEWHIRES /ROW/rss, which returns the element as the root node. We then specify which document we are going to retrieve, by using the [channel/description=80] portion of the XPath expression in brackets to uniquely identify the department with ID 80.

In a Web browser, the XML result of our DBUri request looks like this: 

- <rss version="0.91">
  - <channel>
      <title> Department New Hire Report</title>
...


To see the formatted XML output, use an RSS reader such as Sage, an RSS reader that plugs into Mozilla Firefox through its extensions capability. We add this URL to Sage as an RSS feed, and we get the results shown in Figure 1.

 

figure 1
Figure 1: RSS feed XML displayed in a Sage RSS reader


No Rebuilding Required

Instead of rebuilding all of our databases, unloading our relational rows, and reloading XML documents, we leveraged our investment in existing data models by using XMLType views to generate XML in a powerful, flexible way.


Sean Dillon (sean.dillon@oracle.com) is a principal technologist and has worked for Oracle since 1996. The lead author of Beginning Oracle Programming (APress), Dillon specializes in core database, XML, and Web services technologies.


Send us your comments