Carview!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Internet is driving an unprecedented demand for access to information. Seduced by the convenience of paying bills, booking flights, tracking stocks, checking prices, and getting everything from gifts to groceries online, consumers are hungry for more. Compelled by the lower costs of online outsourcing and the ability to inquire, day or night, "What's the status?," businesses are ramping up to reap the rewards. Excited by improved efficiency and universal public access, governments are considering how all kinds of raw data, from financial reports to federally funded research, can be published online in an easily reusable format.

More than ever before, database-savvy web application developers working to capitalize on these exciting Internet-inspired opportunities need to rapidly acquire, integrate, and repurpose information, as well as exchange it with other applications both inside and outside their companies. XML dramatically simplifies these tasks.

As with any new technology, you first need to understand what XML is, what you can do with it, and why you should use it. With all the new terms and acronyms to understand, XML can seem like a strange new planet to the uninitiated, so let's walk before we run. This chapter introduces "Planet XML" and the "moons" that orbit it, and provides a high-level overview of the tools and technology Oracle offers to exploit the combined strengths of XML and the Oracle database in your web applications.

First, let's look at some basic XML definitions and examples.

XML, which stands for the "Extensible Markup Language," defines a universal standard for electronic data exchange. It provides a rigorous set of rules enabling the structure inherent in data to be easily encoded and unambiguously interpreted using human-readable text documents. Example 1.1 shows what a stock market transaction might look like represented in XML.

Example 1.1. Stock Market Transaction Represented in XML

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

First, let's look at some basic XML definitions and examples.

XML, which stands for the "Extensible Markup Language," defines a universal standard for electronic data exchange. It provides a rigorous set of rules enabling the structure inherent in data to be easily encoded and unambiguously interpreted using human-readable text documents. Example 1.1 shows what a stock market transaction might look like represented in XML.

Example 1.1. Stock Market Transaction Represented in XML

<?xml version="1.0"?>
<transaction>
  <account>89-344</account>
  <buy shares="100">
    <ticker exch="NASDAQ">WEBM</ticker>
  </buy>
  <sell shares="30">
    <ticker exch="NYSE">GE</ticker>
  </sell>
</transaction>

After an initial line that identifies the document as an XML document, the example begins with a <transaction> tag. Nested inside this opening tag and its matching </transaction> closing tag, other tags and text encode nested structure and data values respectively. Any tag can carry a list of one or more named attribute="value" entries as well, like shares=" nn " on <buy> and <sell> and exch=" xxx " on <ticker>.

XML's straightforward "text with tags" syntax should look immediately familiar if you have ever worked with HTML, which also uses tags, text, and attributes. A key difference between HTML and XML, however, lies in the kind of data each allows you to represent. What you can represent in an HTML document is constrained by the fixed set of HTML tags at your disposal—like <table>, <img>, and <a> for tables, images, and anchors. In contrast, with XML you can invent any set of meaningful tags to suit your current data encoding needs, or reuse an existing set that someone else has already defined. Using XML and an appropriate set of tags, you can encode data of any kind, from highly structured database query results like the following:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Beyond encoding data in a textual format, an XML document doesn't do much of anything on its own. The true power of XML lies in the tools that process it. In this section, we take a quick tour of the interesting ways to work with XML documents using tools and technologies widely available today from a number of different vendors.

Since an XML document is just text, you can:

View and edit it with vi, Emacs, Notepad, or your favorite text editor
Search it with grep, sed, findstr, or any other text-based utility
Source-control it using systems like CVS, ClearCase, or RCS

These and other tools can treat an XML file the same as any other text file for common development tasks.

More sophisticated XML editing tools read an XML DTD to understand the lexicon of legal tag names for a particular XML vocabulary, as well as the various constraints on valid element combinations expressed in the DTD. Using this information, the tools assist you in creating and editing XML documents that comply with that particular DTD. Many support multiple views of your XML document including a raw XML view, a WYSIWYG view, and a view that augments the WYSIWYG display by displaying each markup tag.

As an example, this book was created and edited entirely in XML using SoftQuad's XMetal 1.0 product in conjunction with the DocBook DTD, a standard XML vocabulary for authoring technical manuals. Figure 1.2 shows what XMetal looks like in its WYSIWYG view with tags turned on, displaying an earlier version of the XML source document for this very chapter.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Why have vendors like Oracle, IBM, Microsoft, Sun, SAP, and many others moved so fast to support XML? After all, these companies have worked for many years to fine-tune the efficiency of their proprietary data formats and tools. The reason is simple: as a vendor-neutral, platform-neutral, language-neutral technology for web-based data exchange, the XML family of standards solves a key problem for these companies' customers. In a nutshell, XML simplifies the task of connecting applications and services over the Web.

Proprietary data formats undoubtedly represent data in a more efficient way, but what XML sacrifices in compactness, it gains many times over in flexibility. If you can publish an XML datagram on the "wire," anyone connected to the Internet can receive the datagram, parse it, manipulate it, search it, and transform it using a wide selection of freely available tools that implement the XML family of standards. Skeptics who dwell on XML's apparent inefficiencies ("It's more tags than data, for heaven's sake!," they say), haven't yet understood how its usefulness grows when it is combined with the Internet's universal reach. The HTML standard and the essentially free cost of HTML-savvy browsers sparked the creation of the Web as we know it today: a sea of information available 24 hours a day for a pair of human eyeballs focused on a browser to exploit. The XML family of standards and the low cost of many XML-savvy tools have already begun to spark an analogous "data web" of Internet-based machine-to-machine information exchanges. XML is on track to have an even greater impact on the computing landscape than HTML has had.

Critics are correct to point out that XML is no magic bullet, noting that companies still need to agree on common XML vocabularies for application data exchange. However, market forces are already quickly resolving these concerns. Due to the tremendous opportunities presented by business-to-business e-commerce, a burgeoning sector whose key players are already leveraging this new XML-based data web, the number of XML-based standards for domain-specific business messages is growing exponentially. By tapping into the XML data web, you can save money by leveraging outsourced business content and services, and generate new revenue by publishing slices of your own valuable data as web services to be "consumed" by other partners.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Now that we understand what the XML family of standards is, what we can do with it, and why it is interesting to apply to database-driven web applications, let's get an overview of the tools and technologies that Oracle provides to implement Oracle XML applications. Figure 1.11 shows an example of the key Oracle XML components and how they relate to the XML standards we've discussed earlier.

Figure 1.11: Overview of key Oracle technologies for XML

Using the Oracle XML Parser, you can parse XML documents into their infoset, manipulate their contents, and return the modified infoset back into XML format. Using the Oracle XSLT processor, you can transform XML into XML, HTML, or text of another structure. Both the Oracle XML Parser and the Oracle XSLT processor share the common Oracle XPath Engine that enables querying an XML document's infoset to select interesting subsets of its information. The Oracle XML SQL Utility automates the tasks of producing XML from SQL query results and storing XML documents into tables in the database. Oracle interMedia Text enables indexing and querying of XML documents or document fragments, with rich support for leveraging the structure of the XML in the query criteria. These core components are used by more than 40 of Oracle's own internal development teams, so their quality, performance, and conformance to W3C standards are very high.

Building on these core Oracle XML technologies, the Oracle XSQL Pages system is an extensible XML publishing framework that makes combining the strengths of SQL, XML, and XSLT a declarative walk in the park. It simplifies the job of assembling XML data pages from multiple sources and transforming their information content for delivery using XSLT. Table 1.1 provides a summary of key Oracle XML technologies and the chapters that cover each one.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this chapter, I cover the essential technical details you will need to work with XML documents. I also devote a section to learning the powerful XPath language, which you can use to flexibly search XML documents.

Building on the high-level overview in Chapter 1, here we drill down in more detail to some of the specifics of working with XML documents.

As we saw in the examples in Chapter 1, XML can represent virtually any kind of structured information. A coherent set of elements and attributes that addresses a particular application need is called an XML vocabulary. The elements and attributes are the "words" in the vocabulary that enable communication of information on a certain subject. An XML vocabulary can be as simple as a single element—for example a <Task>, or can contain as many elements and attributes as you need. An example document that uses the <Task> vocabulary looks like this:

<Task Name="JDeveloper 3.1">
   <Task Name="Improved XML Support">
     <Task Name="Syntax-Check XML/XSL" Dev="Steve"/>
     <Task Name="Color-Coded Editing"  Dev="Yoshi"/>
     <Task Name="Run XSQL Pages"       Dev="Bret"/>
   </Task>
   <Task Name="Improved Debugging Support">
     <Task Name="Remote Debugging">
       <Task Name="JServer Debugging"      Dev="Jimmy"/>
       <Task Name="Apache JServ Debugging" Dev="Liz"/>
     </Task>
   </Task>
</Task>

One of the big attractions about working with XML is its low cost of admission. The specification is free to be used by anyone, and you only need a text editor to get started. One way to begin creating your own XML vocabulary is to simply start typing tags in a text file as they come to your mind. For example, if you've been assigned the task of managing a "Frequently Asked Questions" (FAQ) list, you might open up vi or Emacs and start typing the example shown in Figure 2.1.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Building on the high-level overview in Chapter 1, here we drill down in more detail to some of the specifics of working with XML documents.

As we saw in the examples in Chapter 1, XML can represent virtually any kind of structured information. A coherent set of elements and attributes that addresses a particular application need is called an XML vocabulary. The elements and attributes are the "words" in the vocabulary that enable communication of information on a certain subject. An XML vocabulary can be as simple as a single element—for example a <Task>, or can contain as many elements and attributes as you need. An example document that uses the <Task> vocabulary looks like this:

<Task Name="JDeveloper 3.1">
   <Task Name="Improved XML Support">
     <Task Name="Syntax-Check XML/XSL" Dev="Steve"/>
     <Task Name="Color-Coded Editing"  Dev="Yoshi"/>
     <Task Name="Run XSQL Pages"       Dev="Bret"/>
   </Task>
   <Task Name="Improved Debugging Support">
     <Task Name="Remote Debugging">
       <Task Name="JServer Debugging"      Dev="Jimmy"/>
       <Task Name="Apache JServ Debugging" Dev="Liz"/>
     </Task>
   </Task>
</Task>

One of the big attractions about working with XML is its low cost of admission. The specification is free to be used by anyone, and you only need a text editor to get started. One way to begin creating your own XML vocabulary is to simply start typing tags in a text file as they come to your mind. For example, if you've been assigned the task of managing a "Frequently Asked Questions" (FAQ) list, you might open up vi or Emacs and start typing the example shown in Figure 2.1.

Figure 2.1: Creating a new XML document using Emacs

It's very useful to just prototype your vocabulary of tags by working directly on an example document. It makes the process easy to think about. As ideas pop into your head—for example, "I'm going to need to keep track of who

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

XML entities and namespaces provide two techniques to modularize the contents of XML documents. In this section we study simple examples of both.

Entities are a mechanism for defining named string substitution variables in your XML file. They can save typing repetitive text in your documents. For example, if the Frequently Asked Questions document that we're working on is for the Oracle JDeveloper 3.1 product, the text Oracle JDeveloper and the current version number of the product might appear in many of the questions and answers. We can define two entities to represent this repetitive text using the syntax:

<!ENTITY jdev "Oracle JDeveloper">
<!ENTITY ver  "3.1">

With these in place we can refer to the entities by name in the contents of our FAQ.xml document with the syntax:

<Question>What is the current version of &jdev;?</Question>
<Answer>The current version is &jdev; &ver;</Answer>

The syntax for user-defined entities is identical to that of the built-in entities we saw earlier (<, >, ', and "). References to user-defined entities like &jdev; start with an ampersand character and end with a semicolon, with the entity name in between. Like the default attribute values we saw earlier, these entities can be defined in an external DTD file, or right in the current document by including them in the local DTD section known as the internal subset . The syntax for including these entity definitions in the document you're working on (when you do not control the DTD or when the entities are not interesting enough to be placed there for global use) looks like this:

<?xml version="1.0"?>
<!DOCTYPE FAQ-List SYSTEM "FAQ-List.dtd"[
  <!-- Internal subset adds local definitions -->
  <!ENTITY jdev "Oracle JDeveloper">
  <!ENTITY ver  "3.1">
]>
<FAQ-List>
  <FAQ Submitter="smuench@oracle.com">
    <Question>Is it easy to get started with XML?</Question>
    <Answer>Yes!</Answer>
  </FAQ>
  <FAQ Submitter="smuench@oracle.com" Level="Beginner">
    <Question>What is the current version of &jdev;?</Question>
    <Answer>The current version is &jdev; &ver;</Answer>
  </FAQ>
</FAQ-List>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you organize files into directories on a disk, you use the directories to give some meaningful structure to your files. Familiar commands like dir or ls allow you to search for matching files that have been organized into those nested directories. Consider the directory hierarchy of drive M: as shown in Figure 2.9 using the Windows Explorer.

Figure 2.9: Browsing hierarchical directory structure

In order to search this hierarchy, you use a path notation to indicate what files you are trying to find. The path notation for searching a Unix filesystem, for example, allows you to use the notation /MovieList/Movie/Cast to refer to the Cast subdirectory under the Movie subdirectory of MovieList, or /MovieList/Movie/Cast/*to refer to any file in that directory.

To list all the files in the Actor subdirectory, you can issue the command:

ls /MovieList/Movie/Cast/Actor/*

which produces this list of matching files:

/MovieList/Movie/Cast/Actor/First
/MovieList/Movie/Cast/Actor/Last
/MovieList/Movie/Cast/Actor/Award

As we saw in Chapter 1, XML documents also have an industry-standard path notation for searching their content, called XPath. You'll find its syntax easy to understand because of its basic similarity to what we saw earlier for files in directories. We'll see with several examples (and throughout this book) that the searches you can do with the XPath notation over an XML document are much more powerful than the simple file listings illustrated above.

Table 2.1 summarizes the syntax for the most common XPath expressions.

Table 2.1: Syntax Summary for the Most Common XPath Expressions
XPath Expression Syntax

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this chapter, we'll get a taste for what the rest of the book is about: making XML a lot more interesting by combining it with the power of your Oracle database. We explore a scenario of a growing company that needs to integrate and share information for a frequently asked questions "knowledge bank" and put the information to use in a variety of formats.

Don't worry if we move quickly through a lot of the details here. You'll learn how to do everything we cover in this rapid-fire walkthrough—and much, much more—in the numerous examples throughout the rest of the book. This chapter is meant as a preview. So buckle yourself in—the ride is about to start.

Recall the frequently asked questions document from Chapter 2:

<?xml version="1.0"?>
<!DOCTYPE FAQ-List SYSTEM "FAQ-List.dtd">
<FAQ-List>
  <FAQ Submitter="smuench@oracle.com">
    <Question>Is it easy to get started with XML?</Question>
    <Answer>Yes!</Answer>
  </FAQ>
  <FAQ Submitter="derek@spinaltap.com" Level="Advanced">
    <Question>Are we going to play Stonehenge?</Question>
    <Answer>But of course</Answer>
  </FAQ>
</FAQ-List>

Let's assume that we're working at a small Internet startup company. We start out with a single, small XML file using the FAQ-List.dtd vocabulary similar to the one described earlier. In the beginning, only a single person is in charge of editing the FAQ file; the number of questions is small, and the number of products we sell is tiny, too. Then, as we develop and ship newer products, we naturally hire more people to help in the process, and now each product team wants to manage its own XML FAQ file. So we split up the frequently asked questions into one XML FAQ file per product team—all still sharing the FAQ-List.dtd format. But inevitably:

Our products get more and more popular.
Our list of frequently asked questions begins to grow.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Recall the frequently asked questions document from Chapter 2:

<?xml version="1.0"?>
<!DOCTYPE FAQ-List SYSTEM "FAQ-List.dtd">
<FAQ-List>
  <FAQ Submitter="smuench@oracle.com">
    <Question>Is it easy to get started with XML?</Question>
    <Answer>Yes!</Answer>
  </FAQ>
  <FAQ Submitter="derek@spinaltap.com" Level="Advanced">
    <Question>Are we going to play Stonehenge?</Question>
    <Answer>But of course</Answer>
  </FAQ>
</FAQ-List>

Let's assume that we're working at a small Internet startup company. We start out with a single, small XML file using the FAQ-List.dtd vocabulary similar to the one described earlier. In the beginning, only a single person is in charge of editing the FAQ file; the number of questions is small, and the number of products we sell is tiny, too. Then, as we develop and ship newer products, we naturally hire more people to help in the process, and now each product team wants to manage its own XML FAQ file. So we split up the frequently asked questions into one XML FAQ file per product team—all still sharing the FAQ-List.dtd format. But inevitably:

Our products get more and more popular.
Our list of frequently asked questions begins to grow.
Our customers begin demanding to search FAQs across all products.
Our customers insist on flexible, fast searching on multiple criteria.
Our competition launches a personalized FAQ portal page for web developers.

Help! We need a powerful database to go with our XML. So we create a faq_table to hold all of our frequently asked questions:

create table faq_table(
  category     VARCHAR2(30),
  question     VARCHAR2(4000),
  answer       VARCHAR2(4000),
  qlevel       NUMBER,
  submitted_by VARCHAR2(80)
);

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We see something interesting in the Oracle XSQL Pages material explaining that we can create a new XSQL page that reuses the dynamic XML content produced by another page with a tag called <xsql:include-xsql> . We also notice that by including one extra line at the top of our page, like this:

<?xml-stylesheet type="text/xsl" href="FAQ-In-XML.xsl"?>

we can get the XSQL page processor to handle the XSLT transformation automatically before it returns the results to the requester. So we combine these two little pearls of information to create a new XSQL page called FAQXML.xsql:

<?xml version="1.0"?>
<!-- FAQXML.xsql: Show FAQ.xsql in Standard FAQ-List.dtd Vocabulary  -->
<?xml-stylesheet type="text/xsl" href="FAQ-In-XML.xsl"?>
<xsql:include-xsql href="FAQ.xsql" xmlns:xsql="urn:oracle-xsql"/>

This uses <xsql:include-xsql> to include the XML results from our previous work, and associates the FAQ-In-XML.xsl stylesheet we created above to this page. Now, when we try from our browser to request the new XSQL page for the jdev category of frequently asked questions, as shown in Figure 3.2, we see our dynamic information in our <FAQ-List> format with no manual steps involved. Pretty neat.

Figure 3.2: Viewing the dynamic FAQ in the FAQ-List XML format

Just then, we learn that our CEO is expanding into Europe by acquiring another promising startup in London. In a parallel universe, the London company had also been working with XML to keep track of customer questions and answers. However, they were following an established British standard XML vocabulary that they found on the Web, described by the Frequently-Posed-Queries.dtd.

We quickly conclude that this is not a problem and offer to host all of their customers' frequently posed queries in our faq_table. We cobble together a FAQ-In-UKFAQ.xsl XSLT stylesheet to transform our

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We check out the ServletsFAQ.xml file on Sun's site, which they update periodically, and notice that it has a format like this:

<?xml version = '1.0' encoding = 'UTF-8'?> 
<Servlets-FAQ>
  <FAQ>
    <Q>What's the next major release of the Servlet API?</Q>
    <A>Servlet API 2.2</A>
  </FAQ>
  <FAQ>
    <Q>How do I set the age of a cookie?</Q>
    <A>yourCookie.setAge(120);</A>
  </FAQ>
  <FAQ>
    <Q>How do I save a variable in a per-user session?</Q>
    <A>request.getSession(true).putValue('varname','value');</A>
  </FAQ>
</Servlets-FAQ>

We want this Servlet FAQ content and others like it from Sun's site to reside in our database so we can search over it together with all of our own content. We learn that the Oracle XML SQL Utility can insert XML documents automatically into our faq_table if they have the canonical <ROWSET>/<ROW> structure, so we create another XSLT stylesheet to transform a document in Sun's <Servlets-FAQ> vocabulary into a <ROWSET>/<ROW> document:

<ROWSET xsl:version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform">
  <xsl:for-each select="/Servlets-FAQ/FAQ">
    <ROW>
      <CATEGORY>SERVLETS</CATEGORY>
      <QLEVEL>1</QLEVEL>
      <QUESTION><xsl:value-of select="Q"/></QUESTION>
      <ANSWER><xsl:value-of select="A"/></ANSWER>
      <SUBMITTED_BY>Sun</SUBMITTED_BY>
    </ROW>
  </xsl:for-each>
</ROWSET>

This time, the tables are turned and our XPath expressions in <xsl:for-each> are looping over Sun's XML format and constructing the <ROWSET>, <ROW>, and column-name elements required for the automatic insert. As in all of our other stylesheets, we use <xsl:value-of> to plug in the Q and A children element values from the current /Servlets-FAQ/FAQ element as the values of our <QUESTION> and <ANSWER> elements. Note that because Sun doesn't keep track of the difficulty level in the FAQ format, we're using a constant value of 1 in our stylesheet to default to a value that makes sense to our system. Also, we use a constant value of SERVLETS for the category to make sure that the information from FAQs for Servlets is placed in an appropriate category for searching through our faq_table.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Whether you want to just work with XML and XSL files or you are a hardcore Java or PL/SQL developer, you'll find that JDeveloper 3.1 has lots of features to make your life easier. A few of the features that I personally use every single day of my life are:

Color-coded syntax highlighting for XML/XSLT editing
Auto-indenting and block indent/unindent to keep XML looking nice
Built-in XML syntax checking
Native support for running XSQL pages
Ability to browse all Oracle8i schema objects, including Java classes
Context-sensitive help as you type for methods and arguments
Fast jumping to source and JavaDoc for any class that pops into your head
Robust remote debugging support for Apache JServ, Tomcat, and others
Robust remote "in-the-database" debugging support for JServer

We'll cover what you need to know to exploit these JDeveloper 3.1 features in this chapter.

JDeveloper 3.1 ships with a number of helpful XML samples. These include sample XSQL pages, Java programs, and servlets to help you make sure your environment is properly set up to run all the examples in this book. Open the JDeveloperXMLExamples.jws workspace in the .\samples\xmlsamples subdirectory of your JDeveloper installation home directory to take a look.

This section describes the many ways JDeveloper helps you work with XML, XSQL and JavaServer Pages.

JDeveloper allows you to create workspaces to facilitate working on many different projects at once. The contents of all the projects in your current workspace are always visible and organized in alphabetical order in the project navigator, as shown in Figure 4.1. At any time, you can click your mouse in the project navigator and begin typing the initial letters of a file you are looking for—for example, the letters

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section describes the many ways JDeveloper helps you work with XML, XSQL and JavaServer Pages.

JDeveloper allows you to create workspaces to facilitate working on many different projects at once. The contents of all the projects in your current workspace are always visible and organized in alphabetical order in the project navigator, as shown in Figure 4.1. At any time, you can click your mouse in the project navigator and begin typing the initial letters of a file you are looking for—for example, the letters myi—and a Search for pop-up appears. JDeveloper incrementally jumps you to the first file matching the letters you've typed in so far, as illustrated in the figure, so typically only a few letters are required to jump to the file you want.

Figure 4.1: Incrementally search for a file in a project

By selecting a project in the navigator and choosing Add Folder... from the right mouse button menu, you can create additional folders to further organize your files within a project.

To add new or existing files to a project, select the target project to make it active (displayed in bold in the navigator). Then click the Add File icon—the folder with a plus sign—at the top of the navigator pane. The File Open/Create dialog appears. Select an existing file or type the name of a new file you want to create, and click the Open button to dismiss the file dialog and add the file to your project.

If you select an existing file in your project before clicking the Add File icon, the file dialog will use the existing file's directory as the current directory for the File Open/Create dialog. This is handy if you'll be adding a file from the same directory as another file in your project.

To delete a file from your project, select the file in the project navigator. Then click the Delete File icon—the folder with a minus sign—at the top of the navigator pane. Confirm the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

All of the examples in this book use a database account named XMLBOOK that you'll need to create to follow along and try out any code we discuss. In this section, we create an XMLBOOK user and explore JDeveloper 3.1's features for working with database objects in the development environment.

To create the XMLBOOK user, connect to your Oracle database as a DBA account like SYS or SYSTEM using the SQL*Plus tool and issue the following commands:

SQL> CREATE USER xmlbook IDENTIFIED BY xmlbook;
User created.
SQL> GRANT connect, resource, query rewrite TO xmlbook;
Grant succeeded.

Then try to connect as the new XMLBOOK user and try a simple SELECT statement by doing the following:

SQL> CONNECT xmlbook/xmlbook
Connected
SQL> SELECT sysdate FROM dual;
SYSDATE
---------
23-APR-00

If this works, then you're ready to go create a named connection for XMLBOOK inside the JDeveloper 3.1 environment.

JDeveloper 3.1 has a number of built-in features for working more easily with Oracle database objects. You can define any number of commonly used database connections that are then available for all workspaces and projects. After starting JDeveloper 3.1, you'll notice a Connections folder at the top of the project navigator. Double-clicking on this folder or selecting Connections... from the right mouse button menu option on the folder brings up the JDeveloper Connection Manager dialog. From here you can create, edit, delete, import, and export connection definitions for databases you frequently work with during development. Click the New... button to define a new connection to work with our XMLBOOK user. The Connection dialog shown in Figure 4.8 appears.

Figure 4.8: Defining a new named connection

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle's XML Developer's Kit (XDK) includes many of the enabling XML technologies that we'll be using in the rest of this book. Among other components, it contains Java and PL/SQL versions of the following:

Oracle XML Parser
Oracle XSLT Processor
Oracle XML SQL Utility

In addition, it includes an Oracle XSQL Servlet that comes with a Java API for adding your own so-called action handlers and programmatically processing XSQL Pages templates. In the following sections, we'll discuss the basics of setting up JDeveloper to work with these Oracle XDK components in Java. While JDeveloper 3.1 does offer color-coded syntax editing of PL/SQL and the ability to browse stored procedures and run the SQL scripts against a named database connection, most of the true developer productivity features in the product target Java development (as you'd assume from the "J" in JDeveloper).

To dramatically simplify working with libraries of Java code in your projects, JDeveloper 3.1 has a facility called named libraries . Each library consists of:

A user-friendly library name like "Oracle XML Parser 2.0."
A Class path comprising one or more .jar files, .zip files, or directories separated by semicolons, containing the executable classes to support the library; for example, J:\lib\xmlparserv2.jar.
An optional Source path comprising one or more .jar files, .zip files, or directories containing the Java source code for the library.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

PL/SQL is Oracle's procedural language extension to SQL, and is the database programming language familiar to nearly all Oracle DBAs and application developers. In this chapter, we'll study lots of examples that illustrate how to perform basic XML processing using PL/SQL. In particular, we'll learn how to:

Load external XML files into the database
Parse XML using the Oracle XML Parser for PL/SQL
Search XML documents in memory using XPath expressions
Post an XML message to another server and get an XML response back
Enqueue and dequeue XML messages from Oracle Advanced Queuing (AQ) queues

In addition, in this chapter we'll cover the basic mechanics of producing XML automatically from SQL queries and transforming the results into any desired XML format using XSLT stylesheets. Both of these topics are covered in full in their own chapters later in the book. For an abbreviated overview of PL/SQL itself, see Chapter 10.

Along the way, we'll build up several useful PL/SQL packages that you can use to simplify basic XML processing from within your own PL/SQL stored procedures, functions, packages, and database triggers. The full source code for these XML helper packages appears in Appendix A, and is available on the O'Reilly web site. Let's dive right in.

When a developer is setting out to store the contents of an XML document in the database to perform queries over its content and structure, one of the first questions that arises is, "How do I get XML files in there?" While in Chapter 12, we explore numerous techniques available for storing XML in the database, here we'll start simple and work through the steps of loading an existing XML file into a CLOB column.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When a developer is setting out to store the contents of an XML document in the database to perform queries over its content and structure, one of the first questions that arises is, "How do I get XML files in there?" While in Chapter 12, we explore numerous techniques available for storing XML in the database, here we'll start simple and work through the steps of loading an existing XML file into a CLOB column.

CLOB (pronounced "klob" or "see-lob") stands for C haracter L arge OB ject. You can think of a column of type CLOB as a very, very large VARCHAR2. It can hold character-based data like XML documents as large as four gigabytes (4GB).

Assume that we have an XML document like claim77804.xml below in a directory named C:\XMLFILES on the filesystem of the machine where our database is installed:

<!-- claim77804.xml -->
<Claim>
  <ClaimId>77804</ClaimId>
  <Policy>12345</Policy>
  <Settlements>
    <Payment Approver="JCOX">1000</Payment>
    <Payment Approver="PSMITH">1850</Payment>
  </Settlements>
  <DamageReport>
    The insured's <Vehicle Make="Volks">Beetle</Vehicle>
    broke through the guard rail and plummeted into a ravine.
    The cause was determined to be <Cause>faulty brakes</Cause>.
    Amazingly there were no casualties.
  </DamageReport>
</Claim>

Since operating systems differ in their file and directory naming conventions, Oracle abstracts these details with a logical directory object. You create a logical directory with the command:

CREATE DIRECTORY directoryname AS 'OS-Specific-Dirname';

You then use the logical directory name directoryname when working with files inside PL/SQL. In order to create a logical directory, use SQL*Plus to connect to the database as a user with the CREATE ANY DIRECTORY privilege. The SYS and SYSTEM users have this privilege by default, so the easiest way to proceed is to connect as SYS or SYSTEM and create the directory with:

CREATE DIRECTORY xmlfiles AS 'C:\xmlfiles';

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Oracle XML Parser for PL/SQL provides PL/SQL developers with a set of APIs for parsing, manipulating, and transforming XML documents inside the database. As we'll see in Chapter 6, these same APIs are available to Java programmers as well using the Oracle XML Parser for Java. As illustrated in Figure 5.1, in Oracle8i Releases 1, 2, and 3—server versions 8.1.5, 8.1.6, and 8.1.7, respectively—the Oracle XML Parser for PL/SQL is a set of PL/SQL packages that expose the underlying functionality of the XML Parser for Java. In the 8.1.7 release, the XML Parser for Java is natively compiled inside the server for better performance.

Figure 5.1: XML Parser for PL/SQL wraps the XML Parser for Java

The packages included in the Oracle XML Parser for PL/SQL are listed in Table 5.1.

Table 5.1: Key PL/SQL Packages for Working with XML
PL/SQL Package Name	Description
`xmlparser`	Contains datatypes, functions, and procedures for parsing XML
`xmldom`	Contains datatypes, functions, and procedures for creating and manipulating the members of an XML document's object model like elements, attributes, text, comments, etc.
`xslprocessor`	Contains datatypes, functions, and procedures for transforming XML documents using XSLT stylesheets and searching an in-memory XML document using XPath expressions

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

While processing XML documents and datagrams inside the database, you'll frequently find it necessary and convenient to search the content of the document you're processing. We've noted that the xmldom package provides a completely programmatic way of hunting through the entire tree of nodes of an XML document to find what you're looking for. We'll see in this section how XPath's searching of in-memory XML makes it all declarative and so much easier.

We saw in Chapter 2, that XPath expressions provide a compact, declarative syntax to describe any parts of an XML document you would like to address. Let's explore what opportunities exist for the PL/SQL developer to exploit XPath expressions to search in-memory documents.

Let's look again at our simple insurance claim XML document:

<!-- claim77804.xml -->
<Claim>
  <ClaimId>77804</ClaimId>
  <Policy>12345</Policy>
  <Settlements>
    <Payment Approver="JCOX">1000</Payment>
    <Payment Approver="PSMITH">1850</Payment>
  </Settlements>
  <DamageReport>
    The insured's <Vehicle Make="Volks">Beetle</Vehicle>
    broke through the guard rail and plummeted into a ravine.
    The cause was determined to be <Cause>faulty brakes</Cause>.
    Amazingly there were no casualties.
  </DamageReport>
</Claim>

We might want to answer the following questions about this document:

What is the value of the policy number for this claim?

We may want to store the policy number for this XML-based insurance claim in its own database column so we can more easily search, sort, and group by that key piece of information. To do this, we need the ability to retrieve the value of the XPath expression:

/Claim/Policy

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll investigate examples of using XML as a structured data exchange mechanism between applications. First we'll look at synchronous approaches like posting and getting XML messages over HTTP; then we'll learn the basics of Oracle's Advanced Queuing (AQ) mechanism to support asynchronous XML message passing between applications using reliable queues.

Since XML can represent structured data in an open, standard way, it is quickly becoming the preferred method of data exchange over the Web. In a year or two, it will be the dominant method. Sites that serve up information in HTML—useful primarily to human eyeballs—will add the ability to retrieve the information in an XML-based format that will allow other servers to use that data more easily. Businesses whose current web-based applications only allow human interaction through web-based HTML forms are scrambling to add the ability to accept posted requests in XML formats to enable Business-to-Business automation.

In this way, the Web will rapidly evolve to offer a business application developer a sea of XML-based information services, where your application can check on the status of an order, cancel a reservation, or book a flight simply by sending and receiving appropriate XML datagrams. It goes without saying that the ability to write programs that post and receive XML is a core competence for any developer building the next generation of web applications. Here we'll explore how these key activities can be done with PL/SQL inside Oracle8i.

To be very precise, when we talk about "posting XML to another server over the Web," what we mean is sending an HTTP POST request to that server, containing an XML document in the request body with a MIME Content-Type of text/xml.

Therefore, a fundamental ingredient in posting XML over the Web is the ability to send an HTTP POST request. Since the HTTP protocol is a set of conventions layered on top of TCP/IP, we can make use of the Oracle8

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll briefly cover the following mechanisms available to PL/SQL in Oracle8i for producing XML from SQL queries and for transforming XML using XSLT transformations:

The XML SQL Utility provides capabilities to automatically deliver the results of any valid SELECT statement as an XML document.
The Oracle XSLT processor implements a transformation engine for XML documents that is compliant with the W3C XSLT 1.0 Recommendation (see https://www.w3.org/TR/1999/REC-xslt-19991116), and that allows you to transform XML in one format into XML, HTML, or plain text of another format.

These topics are covered in detail in Chapter 7, and Chapter 9, so here we will focus mostly on the basic PL/SQL syntax of working with the XML SQL Utility and the Oracle XSLT processor. First, we'll cover the steps required to verify that these facilities are properly installed in your database, then we'll cover simple examples of their use.

First, check to see if the Oracle XML SQL Utility is already installed in your Oracle8i database by doing the following:

Connect to your Oracle8i database with SQL*Plus:
```
sqlplus xmlbook/xmlbook
```

Check the status of the oracle.xml.sql.query.OracleXMLQuery class by running the following SQL statement:

SELECT SUBSTR(dbms_java.longname(object_name),1,35) AS class, status
  FROM all_objects 
 WHERE object_type = 'JAVA CLASS'
   AND object_name = dbms_java.shortname('oracle/xml/sql/query/OracleXMLQuery')

You should see the result:

CLASS                               STATUS
----------------------------------- -------
oracle/xml/sql/query/OracleXMLQuery VALID

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In its relatively brief history, Java has become a dominant programming language for new software development projects and the main language taught to waves of new programmers in universities. Initially conceived as a portable language for client-side agents and user interfaces, Java's most rapid adoption has been for writing complex, server-side applications. Since nearly any interesting server-side application makes heavy use of a relational database, Oracle responded to the strong demand for server-side Java and database integration by introducing Oracle8i 's JServer product and has moved quickly to provide support for Java servlets and Java Server Pages ( JSPs) in its application server offerings. Starting with Oracle8i version 8.1.5, JServer has been provided with the database.

XML emerged in the age of Java and has been nearly inseparable from it. It is frequently said that, " Java is portable code, and XML is portable data"—a natural fit. In fact, from the beginning, the majority of software tools available for processing XML have been Java-based, and that tradition continues today. Vendors like Oracle and IBM—as well as organizations like the Apache Software Foundation—have done all of their XML innovation in Java first, with other language implementations—C, C++, PL/SQL, Perl, and others—being delivered in later phases. Given these dynamics, it's not hard to figure out why Oracle8i 's integration of rich server-side support for the industry's new standard for information exchange (XML) with the most popular server-side programming language ( Java) and the existing standard for data access and manipulation (SQL) has caught a lot of developers' attention. The fact that Java and PL/SQL can be used together seamlessly inside Oracle8i means that existing Oracle developers and DBAs can learn Java at their own pace while new college grads dive headlong into Java.

By the end of this chapter, you'll understand how to combine Java, JDBC, SQL, and XML—both outside and inside Oracle8i—in order to:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Before jumping into XML-specific Java programming with Oracle, you need to understand exactly what Oracle8i JServer is and what options exist for the Java programmer regarding:

Where Java code can be deployed
How the deployed Java code talks to the database
How the deployed Java code can be accessed by clients

Then we'll cover the basics of connecting to the Oracle8i database and the fundamentals of working with CLOBs—Oracle8i 's native datatype for large character data documents like XML documents.

JServer is Oracle's Java virtual machine (VM), the execution environment for Java code that runs in the same process space as the Oracle8i database server. While functionally compatible with any Java VM, JServer was completely written from scratch to exploit and tightly integrate with Oracle's scalable and reliable server infrastructure. This makes Java in Oracle8i a safe choice for server programming. Logging into Oracle8i Release 2 or later using the SQL*Plus command-line tool, we can see that JServer announces itself as a built-in part of the database server:

SQL*Plus: Release 8.1.6.0.0 - Production on Fri Apr 14 21:31:51 2000
(c) Copyright 1999 Oracle Corporation.  All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL>

Of course, Oracle has been a programmable database server since version 7.0, which introduced PL/SQL, but in Oracle8i, Java joins PL/SQL as a peer in this capacity. Any server contexts where PL/SQL can be used—stored procedures, functions, packages, triggers, and object types—can now be written using Java as well. Besides the obvious differences in language syntax, the key difference for programmers between PL/SQL and Java is that Java programs that access Oracle data and process XML can run

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Oracle XML Parser for Java is an amazing little piece of software. It provides everything we need to:

Parse XML documents and DTDs
Validate XML documents against a DTD
Programmatically construct and manipulate XML documents
Search XML documents using XPath expressions
Transform XML documents using XSLT stylesheets

By the end of this chapter, we'll have done all these tasks, but in the next few sections we focus on the first three. First, let's make sure we've got the latest version of the Oracle XML Parser for Java software and that it's properly installed in your Oracle8i database.

To verify that the Oracle XML Parser for Java is properly installed in your Oracle8i database, do the following:

Connect to your Oracle8i database with SQL*Plus:
```
sqlplus xmlbook/xmlbook
```

Check the status of the oracle.xml.parser.v2.DOMParser class by running the following SQL statement:

SELECT SUBSTR(dbms_java.longname(object_name),1,30) AS class, status
  FROM all_objects 
 WHERE object_type = 'JAVA CLASS'
   AND object_name = dbms_java.shortname('oracle/xml/parser/v2/DOMParser')

If you see the result:

CLASS                          STATUS
------------------------------ -------
oracle/xml/parser/v2/DOMParser VALID

then the Oracle XML Parser for Java is already installed and ready to be used. You do not need to complete any further installation steps.

If instead you see the SQL*Plus no rows

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll work through several examples that illustrate the power and productivity offered by the Oracle XML Parser's tightly integrated XPath support. Using these XPath capabilities, we'll see how easy searching, extracting, evaluating, and retrieving the value of the nodes in an XML document can be, avoiding tons of tedious DOM node manipulation code.

Oracle's implementation of the Document Object Model extends the basic DOM APIs to provide some simple yet powerful additional methods that can save you lots of time when writing applications that process XML. These extensions come in the form of the following extra methods on the XMLNode object:

selectNodes (XPathExpr): Returns a DOM NodeList containing the nodes matching an XPath expression
selectSingleNode (XPathExpr): Returns the first DOM node matching an XPath expression
valueOf (XPathExpr): Returns a String with the text value of the nodes matching an XPath expression
print (Writer | OutputStream ): Serializes the node and its children to an output stream as XML markup

These methods are all defined on the XMLNode class that implements the DOM Node interface in the Oracle XML Parser. Since XMLDocument extends from XMLNode

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll learn the basic Java techniques required to exchange XML data:

Over the Web in real time, by posting an XML message over HTTP to another server and immediately receiving an XML-based response
Asynchronously between processes, by enqueuing XML messages into and dequeuing them out of Oracle AQ queues

These two important tasks are fundamental to the implementation of web services, the business-to-business interchange of information using XML message formats and the HTTP protocol.

As we saw in Chapter 1, the general approach for moving information of any kind around the Web involves the exchange via requests and responses of text or binary resources over the HTTP protocol. A requester requests information by using its Uniform Resource Locator (URL) and a server handling requests for that URL responds appropriately, delivering the requested information or returning an error. HTTP's request/response paradigm supports including a resource with the request as well as receiving a resource back in the response, so it's a two-way street for information exchange.

Any resources being exchanged between requester and server are earmarked by a distinguishing MIME type so the receiver can understand what kind of information it is getting. The registered MIME type for XML-based information resources is text/xml . Putting it all together, the phrase "posting XML to another server" means precisely this: sending an HTTP POST request to that server containing an XML document in the request body with a MIME Content-Type of text/xml.

Posting an XML datagram in the request is useful when you need to submit richly structured information to the server for it to provide its service correctly. At other times, simple parameters in the request are enough to get the answer you need. Here are two examples that make the difference clear.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll briefly cover the mechanisms available in Java for producing XML from SQL queries and for transforming XML using XSLT transformations:

The XML SQL Utility provides capabilities to automatically deliver the results of any valid SELECT statement as an XML document.
The Oracle XSLT processor implements an XML transformation engine compliant with the W3C XSLT 1.0 Recommendation (see https://www.w3.org/TR/1999/REC-xslt-19991116) that allows you to transform XML in one format into XML, HTML, or text of another format.

These topics are covered in detail in Chapter 7 and Chapter 9, so here we'll focus mostly on the basic mechanics of working with the XML SQL Utility and the Oracle XSLT processor. First, we cover the steps required to verify that these facilities are properly installed in your database, and then we'll present some simple examples of their use.

First, check to see if the Oracle XML SQL Utility is already installed in your Oracle8i database by doing the following:

Connect to your Oracle8i database with SQL*Plus:
```
sqlplus xmlbook/xmlbook
```

Check the status of the oracle.xml.sql.query.OracleXMLQuery class by running the following SQL statement:

SELECT SUBSTR(dbms_java.longname(object_name),1,35) AS class, status
  FROM all_objects 
 WHERE object_type = 'JAVA CLASS'
   AND object_name = dbms_java.shortname('oracle/xml/sql/query/OracleXMLQuery')

You should see the result:

CLASS                               STATUS
----------------------------------- -------
oracle/xml/sql/query/OracleXMLQuery VALID

If, instead, you see the SQL*Plus

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We've used XSLT stylesheets in previous chapters to transform database-driven XML into HTML pages, XML datagrams of a particular vocabulary, SQL scripts, emails, and so on. If you're a developer trying to harness your database information to maximum advantage on the Web, you'll find that XSLT is the Swiss Army knife you want permanently attached to your belt. In a world where the exchange of structured information is core to your success, and where the ability to rapidly evolve and repurpose information is paramount, Oracle XML developers who fully understand how to exploit XSLT are way ahead of the pack.

XSLT 1.0 is the W3C standard language for describing transformations between XML documents. It is closely aligned with the companion XPath 1.0 standard and works in concert with it. As we'll see in this chapter, XPath let's you say what to transform, and XSLT provides the complementary language describing how to carry out the transformation. An XSLT stylesheet describes a set of rules for transforming a source XML document into a result XML document. An XSLT processor is the software that carries out the transformation based on these rules.

In the simple examples in previous chapters, we have seen three primary ways to use the Oracle XSLT processor. We've used the oraxsl command-line utility, the XSLT processor's programmatic API, and the <?xml-stylesheet?> instruction to associate a stylesheet with an XSQL page. In this chapter, we begin exploring the full power of the XSLT language to understand how best to use it in our applications.

An XSLT stylesheet describes a transformation that operates on the tree-structured infoset of a source XML document and produces a tree of nodes as its output.

Consider a simple XML document like this:

<!-- Emp.xml -->
<ROWSET>
  <ROW num="1">
    <EMPNO>7839</EMPNO>
    <ENAME>KING</ENAME>
  </ROW>
  <ROW num="2">
    <EMPNO>7788</EMPNO>
    <ENAME>SCOTT</ENAME>
  </ROW>
</ROWSET>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

An XSLT stylesheet describes a transformation that operates on the tree-structured infoset of a source XML document and produces a tree of nodes as its output.

Consider a simple XML document like this:

<!-- Emp.xml -->
<ROWSET>
  <ROW num="1">
    <EMPNO>7839</EMPNO>
    <ENAME>KING</ENAME>
  </ROW>
  <ROW num="2">
    <EMPNO>7788</EMPNO>
    <ENAME>SCOTT</ENAME>
  </ROW>
</ROWSET>

A transformation of this document operates on the document's corresponding node tree (shown in Figure 7.1). The tree of nodes for an XML document always starts with a root node that represents the document itself. Child nodes of the root can be the single document element node—<ROWSET>, in our example—as well as comments and processing instructions. Child nodes of the document element can be any combination of text nodes and element nodes, each of which may, in turn, have similar child nodes. This nesting of nodes forms a tree.

Figure 7.1: Node tree for a simple ROWSET document

Remember that an XML document can look like this:

<ROWSET>
  <ROW num="1">
    <X>Y</X>
  </ROW>
</ROWSET>

or it can look like this:

<ROWSET><ROW num="1"><X>Y</X></ROW></ROWSET>

While both expressions contain a logically equivalent element structure, the former example contains additional whitespace (denoted by WS nodes in Figure 7.1) to give it that indented look. Specifically, it contains a carriage return at the end of every line followed by a series of spaces at the start of the next line. When considering an XML document as a tree of nodes, don't forget that the text nodes containing whitespace also count as nodes the same as text like 7788 or SCOTT. Since you can't see it, whitespace is easy to forget about.

To carry out a transformation, an XSLT processor requires two ingredients:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Many useful transformations can be expressed with just a single-root template. We'll examine the single-template stylesheet here, but we'll spend the rest of this chapter learning why there's a world beyond the root template and why it's worth learning about.

All of the stylesheets we've seen so far for transforming XML into HTML either have looked like this:

<xsl:stylesheet version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform"> 
  <!-- The "root" or "main" template -->
  <xsl:template match="/"> 
    <html>
      <body>
        <!-- 
         | Literal result elements and attributes, intermingled with
         | <xsl:for-each>, <xsl:value-of>, attribute value templates, etc.
         +-->
     </body>
    </html>
  <xsl:template>
</xsl:stylesheet>

or have used the simple form of the single-root template stylesheet, which looks like this:

<!-- In the "simple form" of a stylesheet, the root template is implied -->
<html xsl:version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform">
  <body>
     <!-- 
      | Literal result elements and attributes, intermingled with
      | <xsl:for-each>, <xsl:value-of>, attribute value templates, etc.
      +-->
  </body>
</html>

When you see the xsl namespace declaration:

xmlns:xsl="https://www.w3.org/1999/XSL/Transform"

it is natural to think that the XSLT processor will try to access that URL when your stylesheet is processed. However, the declaration is only used as a unique string to identify the namespace for XSLT. If you do not provide this exact string as the namespace URI for the xsl namespace prefix, the XSLT processor will simply ignore <xsl:template>, <xsl:for-each>, <xsl:value-of>, and other elements with the xsl prefix since it will not recognize them as XSLT actions.

Consider the stylesheet in Example 7.1.

Example 7.1. Single-Root Template Stylesheet to Transform Emp.xml to HTML

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The XSLT transformation process described earlier was explained in terms of node trees. These trees of nodes are the logical form that both the source and result of a transformation can take on the "inside" of a transformation being performed by an XSLT processor. However, this is what happens on the outside:

The source document typically begins as a stream of human-readable characters.
The result of the transformation typically needs to be written out as another stream of human-readable characters—for example, to send the result back to a requesting browser or to save the result in a file for later.

The input to an XSLT transformation must be a tree of source nodes produced by either parsing a well-formed XML document or creating the tree programmatically (for example, via DOM or SAX APIs).

All XSLT transformations process the source node tree to produce a tree of result nodes. If multiple transformations are being applied in sequence by your application, the result tree of one transformation becomes the source tree of the next transformation in sequence. When no more transformations need to be done, the final tree of result nodes needs to be written out as a stream of characters again. This process is called serializing the result tree.

Simple-form stylesheets take advantage of default serialization rules described by the XSLT 1.0 specification to make common cases simple. They serialize transformed output in the default UTF-8 character set and support either of the following output formats:

Indented, properly formatted HTML output, with a media type of text/html
Non-indented XML output with no DOCTYPE and a media type of text/xml

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As we've learned, a stylesheet is a set of rules. When you use only a single-root template, your stylesheet has, accordingly, only a single rule: "When you see the root of the source document, do everything inside this!"

As we'll learn in this section, this strategy has pros and cons similar to those of adopting an "everything in a single main( ) method" coding style in Java:

public class doit {
  public static void main( ) (String[] args) {
    // When this program runs, do everything inside this!
  }
}

Developers learning Java find it easy to start with this simple approach, but they quickly find themselves writing repetitive code that would be nice to factor into helper methods. When this occurs, they would like to stand on the shoulders of other developers by extending others' existing work, overriding just the methods that need to behave differently.

We'll see that there is a nice conceptual analogy between methods in Java classes and templates in XSLT stylesheets. In Java, methods are both the unit of behavior and the unit of overriding. If you write a class with all of the programming logic in a single main( ) method, then someone extending your class can only override that single main( ) method. This means they have to rewrite all the logic just to change one small behavioral aspect. The more effectively a class's methods are logically factored to represent the set of subtasks the class must perform, the easier it is to reuse a single useful method when appropriate, and the easier it is to override just a part of the existing behavior, if necessary.

In XSLT, templates are the unit of behavior as well as the unit of overriding. Similar to the Java analogy above, if you write a stylesheet with all of the transformation logic in a single-root template, then someone extending your stylesheet can only override that entire template to change the way your transformation behaves. The more effectively a stylesheet's templates are logically factored to reflect the individual transformation tasks to be performed, the easier it is to reuse a single useful template when appropriate, and the easier it is to override just a part of the existing transformation behavior, if necessary.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Once you discover the advantages of working with database information in XML, you'll find yourself building XML datagrams from SQL statements for many common tasks. In addition to providing XML components that simplify this job for developers, Oracle has anticipated a much broader interest in serving database-driven XML, and has provided an easier route for non-programmers. Using Oracle's XSQL Pages facility, anyone who is familiar with SQL can quickly and easily build XML data pages from easy-to-author templates.

XSQL pages are templates in *.xsql files that allow anyone familiar with SQL to declaratively:

Assemble dynamic XML "data pages" based on one or more parametrized SQL queries
Transform a data page to produce a final result in any desired XML, HTML, or text-based format using an associated XSLT stylesheet

These templates are processed by a common XSQL page processor engine shared by the XSQL Servlet, the XSQL command-line utility, and the programmatic API for processing XSQL pages called the XSQLRequest class, as shown in Figure 8.1.

Figure 8.1: Overview of XSQL Pages architecture

As this chapter will illustrate, you can accomplish surprising things in a declarative way by combining SQL, XML, and XSLT.

As part of the Oracle XML Developer's Kit, Oracle XSQL Pages is included in Oracle JDeveloper 3.1 and is pre-installed and ready to use with Oracle Internet Application Server and Oracle8i Release 3. In addition, the technology is designed to work with any servlet engine (Apache JServ, Tomcat, JRun, ServletExec, etc.) and can be downloaded from the Oracle Technology Network web site at https://technet.oracle.com/tech/xml

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

XSQL pages are templates in *.xsql files that allow anyone familiar with SQL to declaratively:

Assemble dynamic XML "data pages" based on one or more parametrized SQL queries
Transform a data page to produce a final result in any desired XML, HTML, or text-based format using an associated XSLT stylesheet

These templates are processed by a common XSQL page processor engine shared by the XSQL Servlet, the XSQL command-line utility, and the programmatic API for processing XSQL pages called the XSQLRequest class, as shown in Figure 8.1.

Figure 8.1: Overview of XSQL Pages architecture

As this chapter will illustrate, you can accomplish surprising things in a declarative way by combining SQL, XML, and XSLT.

As part of the Oracle XML Developer's Kit, Oracle XSQL Pages is included in Oracle JDeveloper 3.1 and is pre-installed and ready to use with Oracle Internet Application Server and Oracle8i Release 3. In addition, the technology is designed to work with any servlet engine (Apache JServ, Tomcat, JRun, ServletExec, etc.) and can be downloaded from the Oracle Technology Network web site at https://technet.oracle.com/tech/xml. You'll find complete installation instructions for using XSQL Pages with several popular servlet engines in Appendix B.

An XSQL page template is an XML document that uses <xsql:query> tags anywhere you want dynamic XML query results to be included. By convention, the template lives in a file with an .xsql extension. To serve new kinds of XML datagrams as new needs arise, you simply build new XSQL pages rather than writing, compiling, debugging, and deploying custom programs or servlets every time. The Oracle XSQL Servlet does the dirty work of handling requests for any of your XSQL pages automatically.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Since the data page that results from executing the queries in your XSQL page template is an XML document, and since XSLT offers a robust, declarative mechanism for transforming XML documents into anything else, we can put the two together to achieve a highly effective one-two punch:

The XSQL page assembles XML information relevant to the task at hand.
The XSLT stylesheet transforms it into an appropriate format for delivery.

We'll look at a few examples in this section that illustrate this approach.

To associate an XSLT stylesheet with an XSQL page, we use the standard technique prescribed by the W3C (see https://www.w3.org/TR/xml-stylesheet), the <?xml-stylesheet?> processing instruction. It goes after the <?xml version="1.0"?> declaration and before the document element in your XSQL page. It must include as attributes two key pieces of information required to identify the stylesheet:

An href attribute that describes the absolute or relative location of the desired stylesheet
A type= "text/xsl" attribute that indicates the type of stylesheet in use

Including an <?xml-stylesheet?> instruction of type "text/xsl" in your XSQL page causes the XML document obtained by expanding the XSQL page's <xsql:query> tags to be transformed by the indicated stylesheet in the server before returning the results to the requester. Only an <?xml-stylesheet?> instruction with an attribute of type="text/xsl" will be considered for use, since the XSQL page processor supports only this type of stylesheet.

A simple example of an XSQL page that includes a reference to an XSLT stylesheet is the following:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

While you're developing your XSQL pages, if for any reason you receive an unexpected result, the following techniques may help diagnose the problem.

If your page does not include any <?xml-stylesheet?> instructions, there are only a few problems that can affect the page:

If you request the page and receive a result that still has your <xsql:query> tags in it, the problem is one of the following:
- You are running an XSQL page from within the JDeveloper 3.1 environment and have not properly included the "XSQL Runtime" library in your current project's library list. To remedy this, select Project → Project Properties... from the main JDeveloper 3.1 menu, and select the Libraries tab of the Project Properties dialog. Click the Add button and select the "XSQL Runtime" library from the list to add it to your project.
- You are running the XSQL Servlet using a servlet engine that has not been correctly configured to associate the oracle.xml.xsql.XSQLServlet class to the file extension *.xsql. To remedy, see Appendix B for instructions on setting this up correctly for your servlet engine.
- You have used an incorrect namespace URI to define the xsql namespace prefix for use on your <xsql:query> elements. To remedy, double-check that your namespace declaration is exactly as follows:
```
xmlns:xsql="urn:oracle-xsql"
```

If your returned "data page" contains <xsql-error> elements like:

<xsql-error action="xsql:query">
  <message>No connection attribute specified on document element.</message> 
</xsql-error>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this chapter, I delve into some of the most common kinds of transformations you will want to do on datagrams. In the process, we'll explore several XSLT features and techniques you can use to perform these transformations.

This section explains how to use XSLT variables and how to steer clear of some common traps that first-time XSLT developers run into when trying to use them.

XSLT variables allow you to assign a meaningful name to the value of an XPath expression or result tree fragment and to refer to that value by name later. The value assigned to a variable can be any of the four basic XPath data types (string, number, boolean, and node-set) or a literal tree of XML nodes.

Defining a variable is easy. You just use:

<xsl:variable name="varname" select="XPathExpression"/>

to assign the value of the XPathExpression to the variable named varname, or the alternative syntax:

<xsl:variable name="varname">
  <some>
    <nodes val="xx">
      <here/>
    </nodes>
  </some>
</xsl:variable>

If you use the latter syntax, and if the tree of nodes you include between <xsl:variable> and </xsl:variable> is a single text node like this:

<xsl:variable name="varname">Some Value</xsl:variable>

then this is equivalent to setting the variable to a string value containing the value of that text node.

A common mistake developers make when assigning a string value to a variable is to use:

<xsl:variable name="var" select="MyString"/>

This is not syntactically wrong, but it does not do what you think. Recall that the value of the select attribute on <xsl:variable> is an XPath expression, and that the XPath expression MyString selects the set of <MyString> elements that are children of the current node. So assuming that your source document does not have any <MyString> elements that are children of the current node, the variable

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section explains how to use XSLT variables and how to steer clear of some common traps that first-time XSLT developers run into when trying to use them.

XSLT variables allow you to assign a meaningful name to the value of an XPath expression or result tree fragment and to refer to that value by name later. The value assigned to a variable can be any of the four basic XPath data types (string, number, boolean, and node-set) or a literal tree of XML nodes.

Defining a variable is easy. You just use:

<xsl:variable name="varname" select="XPathExpression"/>

to assign the value of the XPathExpression to the variable named varname, or the alternative syntax:

<xsl:variable name="varname">
  <some>
    <nodes val="xx">
      <here/>
    </nodes>
  </some>
</xsl:variable>

If you use the latter syntax, and if the tree of nodes you include between <xsl:variable> and </xsl:variable> is a single text node like this:

<xsl:variable name="varname">Some Value</xsl:variable>

then this is equivalent to setting the variable to a string value containing the value of that text node.

A common mistake developers make when assigning a string value to a variable is to use:

<xsl:variable name="var" select="MyString"/>

This is not syntactically wrong, but it does not do what you think. Recall that the value of the select attribute on <xsl:variable> is an XPath expression, and that the XPath expression MyString selects the set of <MyString> elements that are children of the current node. So assuming that your source document does not have any <MyString> elements that are children of the current node, the variable var will be assigned a value that is an empty node-set. To assign the string 'MyString' to the variable var, you need to include the value in quotes to be treated as a literal string and not as a pattern matching an element name:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The XSLT identity transformation is a stylesheet that transforms a document into itself. It does this using a single template that matches any node or any attribute ("node( )|@*") and whose content is the single <xsl:copy> action that copies the current node. Example 9.1 shows what the identity transformation looks like:

Example 9.1. The XSLT Identity Transformation

<!-- The Identity Transformation -->
<xsl:stylesheet version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform">
  <!-- Whenever you match any node or any attribute -->
  <xsl:template match="node( )|@*">
    <!-- Copy the current node -->
    <xsl:copy>
      <!-- Including any attributes it has and any child nodes -->
      <xsl:apply-templates select="@*|node( )"/>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>

The <xsl:copy> action copies the current node and its namespaces; however, by default it doesn't copy any of the current node's attributes or child nodes. You indicate which attributes and child nodes should be copied by instantiating them in the body of the <xsl:copy> element. In the case of the identity transformation, the template selects all attributes and all child nodes to be included recursively.

Whenever you want to make global changes to a document but leave its fundamental structure intact, the identity transformation is a great place to start. By importing the Identity.xsl stylesheet into your new transformation, you can provide additional templates that effect the changes you want, while the identity transformation's

match="node(
)|@*"

template copies the rest of the structure intact.

For example, consider the following simple ItemList.xml document:

<ItemList>
  <Item Id="1234" Color="Red">
    <Description>Singular Red Hat</Description>
    <Price>15.00</Price>
  </Item>
  <Item Id="9876" Color="Blue">
    <Description>Blue Jeans</Description>
    <Price>25.00</Price>
  </Item>
</ItemList>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

One of the most common questions developers learning XSLT ask is, "How can I use XSLT to group repeating information?" In this section, we'll learn how to exploit the database to do the bulk of the grouping and sorting for us, and then use XSLT to format the information. In situations where you don't have the luxury of formulating the database query yourself—for example, if you receive the XML document from a business partner—you still need to perform grouping and sorting. In the following section, we outline techniques for performing the grouping in your stylesheet, including a state-of-the-art approach that uses XSLT "keys" like database indexes to increase by an order of magnitude the speed of traditional pure XSLT grouping techniques. Either way, by the end of this chapter, you'll be aware of all the grouping approaches available and which is best for the task at hand.

If we are producing XML documents from SQL query results, we can leverage the native grouping functionality provided by Oracle to deliver the results straight from the database grouped the way we want. Let's start with a simple XSQL page that queries information about software bugs and the developer who is assigned to fix them:

<xsql:query connection="xmlbook" rowset-element="open-bugs" row-element="bug"
            id-attribute="carview.php?tsp=" tag-case="lower" xmlns:xsql="urn:oracle-xsql" >
  SELECT ID,ABSTRACT,OWNER,PRIORITY FROM BUG
  ORDER BY PRIORITY
</xsql:query>

Requesting this XSQL page produces a result like the one shown in Example 9.5.

Example 9.5. Result from XSQL Bug Query Page

<open-bugs>
   <bug>
      <id>10677</id>
      <abstract>NullPointerException Entering Blank Name Value</abstract>
      <owner>rjust</owner>
      <priority>1</priority>
   </bug>
   <bug>
      <id>11601</id>
      <abstract>Expanding Node Makes Project Tree Flicker</abstract>
      <owner>echan</owner>
      <priority>2</priority>
   </bug>
   <!-- etc. -->
   <bug>
      <id>10445</id>
      <abstract>Resizing Methods Panel Doesn't Resize Control</abstract>
      <owner>rjust</owner>
      <priority>3</priority>
   </bug>
</open-bugs>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you do not have the luxury of controlling the SQL statement producing the XML information you are working with, XSLT 1.0 can be used to sort and group any information in an XML document. The <xsl:sort> action allows you to sort your node lists—either selected by <xsl:for-each> or matched through <xsl:apply-templates> —based on any string or number values that they contain. However, such a claim cannot be made for grouping repeating information. In short, there is no <xsl:group> action that provides this capacity.

The <xsl:sort> action allows you to sort by string or number values as a part of your transformation. Let's look at some examples of how you can put this action to use. Suppose we have a simple shopping list document like this:

<!-- ShoppingList.xml -->
<list>
  <item maker="Friendly Foods">
    <name>Granola Crust Pizza</name>
    <price>11.99</price>
  </item>
  <item maker="Ammondale Farms">
    <name>Yogurt</name>
    <price>1.00</price>
  </item>
  <item maker="Ammondale Farms">
    <name>ChocoFlakes</name>
    <price>3.49</price>
  </item>
</list>

We can create a stylesheet to transform the shopping list into an equivalent structure ordered by the name of the items. To use <xsl:sort>, you include it as a child element of either <xsl:apply-templates> or <xsl:for-each>. The following SortByName.xsl stylesheet illustrates using <xsl:sort> with <xsl:apply-templates> to cause the XSLT processor to process the <item> child elements of the <list> in sorted order based on the value of the <name> element:

<!-- SortByName.xsl: Sort shopping-list items by name -->
<xsl:stylesheet version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform">
  <xsl:output indent="yes"/>
  <xsl:template match="list">
    <xsl:copy>
      <xsl:apply-templates select="item">
        <xsl:sort select="name"/>
      </xsl:apply-templates>
    </xsl:copy>
  </xsl:template>
  <xsl:template match="item">
    <xsl:copy-of select="."/>
  </xsl:template>
</xsl:stylesheet>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Developers using the Oracle database, the Oracle Developer toolset, or any of Oracle's family of packaged applications are likely to be familiar with Oracle's built-in procedural language, PL/SQL. Conceived as a language for data processing, PL/SQL features seamless integration with SQL and makes quick work of virtually any task that involves programmatically querying or modifying database data. This chapter covers the basic techniques available in PL/SQL for delivering XML datagrams as part of your Oracle XML applications. Even Java developers may find PL/SQL comes in handy for some database stored procedures and triggers, so we start with a quick overview of PL/SQL.

In releases of the Oracle database prior to Oracle8i, PL/SQL is the language for server-side application code. In Oracle8i, where Java and PL/SQL may be used interchangeably for server-side development, even Java developers may find PL/SQL easier to use for certain data-intensive tasks.

Here we take a rapid tour of the bare necessities required to understand the structure of PL/SQL programs and to put the basic PL/SQL language constructs to work for XML data publishing.

For more detailed information on PL/SQL, see the O'Reilly Oracle Center online at https://oracle.oreilly.com/ for numerous authoritative titles on the subject, including Oracle PL/SQL Programming, second edition, by Steven Feuerstein and Bill Pribyl.

PL/SQL programs are organized into code blocks that define the scope in which declared variables are visible and in which exceptions can be handled. A block of PL/SQL code is delimited with the BEGIN and END keywords. Any code block may include a declaration section where locally scoped variables are declared, as well as an exception section where exceptions are handled. Blocks of PL/SQL code can be nested to any level.

Example 10.1 shows a simple block of PL/SQL code with both a declaration section and an exception section.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In releases of the Oracle database prior to Oracle8i, PL/SQL is the language for server-side application code. In Oracle8i, where Java and PL/SQL may be used interchangeably for server-side development, even Java developers may find PL/SQL easier to use for certain data-intensive tasks.

Here we take a rapid tour of the bare necessities required to understand the structure of PL/SQL programs and to put the basic PL/SQL language constructs to work for XML data publishing.

For more detailed information on PL/SQL, see the O'Reilly Oracle Center online at https://oracle.oreilly.com/ for numerous authoritative titles on the subject, including Oracle PL/SQL Programming, second edition, by Steven Feuerstein and Bill Pribyl.

PL/SQL programs are organized into code blocks that define the scope in which declared variables are visible and in which exceptions can be handled. A block of PL/SQL code is delimited with the BEGIN and END keywords. Any code block may include a declaration section where locally scoped variables are declared, as well as an exception section where exceptions are handled. Blocks of PL/SQL code can be nested to any level.

Example 10.1 shows a simple block of PL/SQL code with both a declaration section and an exception section.

Example 10.1. Sample PL/SQL Code Block

DECLARE
  symbol    VARCHAR2(40);
  price     NUMBER;
  lastTrade DATE;
BEGIN
  SELECT ticker_symbol, selling_price, trade_date
    INTO symbol, price, lastTrade
    FROM stock_trades
   WHERE transaction_id = 12345;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    MyErrorHandlingProcedure(12345);
END;

The database server can parse, compile, and execute a block of PL/SQL code on the fly, typed in by hand, or run from a script file. You can also create stored procedures and functions in PL/SQL that allow a block of PL/SQL code to easily accept any number of parameters and to be easily reused by name by other database programs. For improved modularity, groups of one or more PL/SQL procedures and functions can be grouped into packages, allowing a clean separation of the programming interface of the reusable routines from their implementation.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We could certainly continue with PL/SQL examples, demonstrating how to write stored procedures to:

Format the query results from multiple SQL statements into a single resulting XML page
Generically process any SQL query, using the built-in DBMS_SQL package, generating appropriate XML tags for their column names
Automatically search for additional information in related tables by checking the database dictionary views for metadata about foreign key and primary key constraints

But luckily, we don't have to write this code ourselves, since Oracle provides all this functionality in the freely downloadable set of XML Utilities for PL/SQL called PLSXML which includes lots of demos and source code (see https://technet.oracle.com/tech/xml/info/plsxml/xml4plsql.htm). The readme.html file in the readme directory in the PLSXML distribution provides setup instructions.

The heart of the PLSXML suite of utilities is a PL/SQL package called DBXML. The package offers a key procedure named Query that accepts a SQL query to be processed and automatically produces the XML output in the OWA page buffer. As Example 10.11 illustrates, it is practically no work at all to use Dbxml.Query. Passing any query to it as a string causes the appropriately formatted XML document representing its query results to be sent to the HTP page buffer.

Example 10.11. Automatically Producing Stock Quote XML with DBXML

CREATE PROCEDURE StockQuotesDbxmlBasic( id NUMBER ) IS
BEGIN
  Dbxml.Query('SELECT q.symbol as Symbol, 
                      q.price  as Price, 
                      q.change as Change
                 FROM quotes q, portfolio_stocks ps
                WHERE q.symbol = ps.symbol
                  AND ps.owner = ' || id);
END;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Java programmers using Oracle8i have a myriad of options for outputting database information as XML. This chapter covers all of the available approaches, proceeding in order from the most manual to the most automatic, with examples every step of the way.

In this section, we'll learn the basics of using Java to generate XML datagrams containing database query results. The two basic programmatic techniques for accessing SQL query results from Java are the JDBC API and SQLJ.

The most basic way in Java to produce XML from database information is to use a JDBC ResultSet to execute a SQL statement and loop over its results. Developers familiar with database programming using the JDBC interface will find this technique to be second nature. Example 11.1 issues a query to retrieve current stock quotes for all the positions in a particular customer's portfolio. Most interesting queries in an application depend on some kind of context information being supplied. Example 11.1 shows how to use a bind variable in the SQL statement, setting the value of the bind variable to the customer id passed in as a command-line argument. This allows the same SQL statement to be used for retrieving the appropriate stock quotes in any customer's portfolio.

Example 11.1. Using a JDBC Result Set to Produce XML

import java.sql.*; 
 
class StockQuotesXmlJdbc 
{ 
  public static void main (String arg[]) throws Exception 
  { 
    // Connect to the database
    Connection cn = Examples.getConnection( );
    // Prepare the query statement containing a bind variable "?"
    PreparedStatement ps =
      cn.prepareStatement("SELECT q.symbol, q.price, q.change" +
                          "  FROM quotes q, portfolio_stocks ps" +
                          " WHERE q.symbol = ps.symbol" +
                          "   AND ps.owner = ?");
    // Use first command line arg as customer id
    int id = Integer.parseInt( arg[0] );
    // Bind value of customer id to first (and only) bind variable
    ps.setInt( 1, id );
    // Execute the query
    ResultSet rs = ps.executeQuery( );
    // Generate the XML document
    System.out.println("<?xml version=\"1.0\"?>");
    System.out.println("<Quotes>");
    // Loop over the rows in the query result
    while (rs.next ( )) {
    System.out.println("<Quote>");
      System.out.println("<Symbol>" + rs.getString(1) + "</Symbol>");
      System.out.println( "<Price>" + rs.getString(2) + "</Price>") ;
      System.out.println("<Change>" + rs.getString(3) + "</Change>");
    System.out.println("</Quote>");
    }
    System.out.println("</Quotes>"); 
    rs.close(); ps.close( ); cn.close( ); 
  } 
}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll learn the basics of using Java to generate XML datagrams containing database query results. The two basic programmatic techniques for accessing SQL query results from Java are the JDBC API and SQLJ.

The most basic way in Java to produce XML from database information is to use a JDBC ResultSet to execute a SQL statement and loop over its results. Developers familiar with database programming using the JDBC interface will find this technique to be second nature. Example 11.1 issues a query to retrieve current stock quotes for all the positions in a particular customer's portfolio. Most interesting queries in an application depend on some kind of context information being supplied. Example 11.1 shows how to use a bind variable in the SQL statement, setting the value of the bind variable to the customer id passed in as a command-line argument. This allows the same SQL statement to be used for retrieving the appropriate stock quotes in any customer's portfolio.

Example 11.1. Using a JDBC Result Set to Produce XML

import java.sql.*; 
 
class StockQuotesXmlJdbc 
{ 
  public static void main (String arg[]) throws Exception 
  { 
    // Connect to the database
    Connection cn = Examples.getConnection( );
    // Prepare the query statement containing a bind variable "?"
    PreparedStatement ps =
      cn.prepareStatement("SELECT q.symbol, q.price, q.change" +
                          "  FROM quotes q, portfolio_stocks ps" +
                          " WHERE q.symbol = ps.symbol" +
                          "   AND ps.owner = ?");
    // Use first command line arg as customer id
    int id = Integer.parseInt( arg[0] );
    // Bind value of customer id to first (and only) bind variable
    ps.setInt( 1, id );
    // Execute the query
    ResultSet rs = ps.executeQuery( );
    // Generate the XML document
    System.out.println("<?xml version=\"1.0\"?>");
    System.out.println("<Quotes>");
    // Loop over the rows in the query result
    while (rs.next ( )) {
    System.out.println("<Quote>");
      System.out.println("<Symbol>" + rs.getString(1) + "</Symbol>");
      System.out.println( "<Price>" + rs.getString(2) + "</Price>") ;
      System.out.println("<Change>" + rs.getString(3) + "</Change>");
    System.out.println("</Quote>");
    }
    System.out.println("</Quotes>"); 
    rs.close(); ps.close( ); cn.close( ); 
  } 
}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The examples we've encountered so far in this chapter show that using Java to produce XML for database query results is straightforward. However, all of the examples we've seen so far print the XML datagram to the standard "console" output stream System.out. While this makes sense for use on the command line and in scripts, it is one step short of what we need for real Oracle XML applications. For these to be effective, we need to serve application information in XML over the Web.

Leveraging the work we've already done in the StockQuotesXml class from Example 11.3, we can produce dynamic stock quote datagrams for web delivery by extending HttpServlet in a class called StockQuotesXmlServlet and performing these simple steps inside its overridden doGet method:

Set the MIME type of the servlet's response to text/xml
Retrieve the customer id from a URL parameter instead of a command-line argument
Call StockQuotesXml.print() to produce the XML for the stocks in the customer's portfolio
Pass the output stream to the Servlet's HttpServletResponse object instead of System.out

The StockQuotesXmlServlet class in Example 11.6 shows the code that gets the job done.

To run a Java servlet from JDeveloper 3.1, just select Run from the right mouse button menu in the project navigator. JDeveloper will launch a single-user web server on port 7070 and start your default browser to exercise the running servlet code. You can also debug servlet code by clicking on the Debug menu option instead of Run.

Example 11.6. Returning XML Stock Quotes Using a Servlet

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In Example 11.2, we wrote a class that produces the XML output of any SQL query using the JDBC ResultSetMetaData class. This example takes a straightforward approach, producing the XML representation for all of the rows of a ResultSet with columns of type NUMBER, DATE, and VARCHAR2. In practice, developers need a more robust solution that supports the full range of Oracle8i object-relational SQL features, including the ability to query user-defined datatypes. They need better control over the number of rows retrieved and the format of the output tags. Extending our XMLForResultSet class to offer generic support for these additional requirements would be a non-trivial task. Luckily, we don't have to build this code ourselves since Oracle provides the XML SQL Utility for Java, which includes the OracleXMLQuery component that automates the entire job.

OracleXMLQuery is a utility class that automates the task of producing XML from SQL query results. It gracefully handles all SQL queries, returning XML for result sets containing both scalar datatypes and user-defined object types in their row values. It offers numerous handy options to control the XML output and is equally adept at producing query results as XML text for web delivery, and in-memory Document Object Model (DOM) tree structures.

To exploit the facilities of the XML SQL Utility, you can use the OracleXML command-line program or you can use the OracleXMLQuery class in your own custom programs. To use the command-line program, ensure that the Java archive files for the XML SQL Utility, the Oracle XML Parser version 2, and the Oracle JDBC driver are in your CLASSPATH, and invoke the command:

java OracleXML

to see the command-line options. The first argument can either be the keyword getXML to exercise the functionality of retrieving XML from SQL, or putXML to store an XML file into a table or view. We'll see more of the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As we'll see in this chapter, XML datagrams of any shape and size can be stored in the database via straightforward techniques using Oracle's XML SQL Utility. Once stored, any or all of the data can be retrieved and delivered on request using XSQL pages and XSLT transformations. This means that XML can be the lingua franca for data exchange outside the database without sacrificing any flexibility, scalability, or performance for data retrieval, processing and management inside the database.

As we've seen in previous chapters, XML datagrams are documents with a rigid, predictable structure, typically comprised of nested elements and attributes, as Example 12.1 shows.

Example 12.1. Credit Card Authorization Datagram

<?xml version="1.0"?>
<!DOCTYPE AuthorizationRequest SYSTEM "creditcardauth.dtd">
<AuthorizationRequest>
  <CardNumber>4678223355451001</CardNumber>
  <Expiration>10/2001</Expiration>
  <Amount Currency="ITL">118000</Amount>
  <MerchantId>84592342</MerchantId>
  <Date>27-12-1999 11:27:04</Date>
</AuthorizationRequest>

In contrast to the rigid structure of XML datagrams, XML documents that represent the written word in some form—technical manuals, contracts, legal proceedings, screenplays, news articles, and others—often contain text mixed with embedded markup tags. Often, a datagram will contain a fragment of this mixed text and markup as an embedded structure, like the <Description> of the <Book> in Example 12.2.

Example 12.2. Datagram with a Mixed Markup Document Fragment

<?xml version="1.0"?>
<Book>
   <ISBN>0395415012</ISBN>
   <Title>Sunrise With Seamonsters</Title>
   <Price>12.75 </Price>
   <Author>
      <First>Paul</First>
      <Last>Theroux</Last>
   </Author>
   <Description>
     Published by <Pub>Houghton Mifflin Co</Pub> in
     <Year>1985</Year>, this <Ed>First</Ed> Edition, 
     has a blue and grey <Cov>Hard</Cov> cover. 
     <Damage>Light wear to top of spine</Damage>.
     <DJ>Very good</DJ> dust jacket.
   </Description>
</Book>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

There are three basic approaches to storing XML information in an Oracle database:

You can store an XML document's information content broken down into the columns of one or more databases tables.
You can store the entire XML document intact in a single column in the database, with an ID value (like the ISBN number in the book example) that plays the role of the document ID.
You can blend the two approaches to:
- Store information from elements containing pure data in their own database columns while storing elements containing "document chunks" intact as document fragments
- Store the entire XML document intact but save some metadata about the document in separate, individual columns

Figure 12.1 illustrates an example of the first approach. The <Book> datagram is stored into a combination of the BOOK and AUTHOR tables in the database. Since the data is in relational tables, it can be queried using SQL. If a <Book> datagram needs to be retrieved from the server, a join query over BOOK and AUTHOR produces the necessary data, which you can transform into a <Book> datagram using an XSLT stylesheet.

Figure 12.1: Storing datagram into one or more relational tables

Figure 12.2 shows an example of the second approach. The <Book> datagram is stored in a single Character Large Object (CLOB) column, and the <ISBN> number functions as the primary key for the document "row" in the table. As we'll see in Chapter 13, the document column can be indexed using the Text component of interMedia to enable precise textual searches on the XML document, leveraging its XML element structure, attributes, and nested element hierarchy. Using the integrated InterMedia search operators in SQL, you can locate any books by Paul Theroux that have "Light wear" cited in the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Oracle XML SQL Utility is a command-line utility and a set of Java classes used to automate getting XML out of your database and putting XML into your database. We have seen that the OracleXMLQuery class handles all the details of getting SQL query results out of the database in a canonical XML format. The XML SQL Utility also contains a companion class called OracleXMLSave that performs the opposite job of putting XML information back into Oracle tables or views.

The OracleXMLSave class understands how to insert any information that OracleXMLQuery knows how to produce. In other words, the canonical structure of the output from OracleXMLQuery defines the kinds of structures for input that OracleXMLSave can automatically insert for us.

The fact that OracleXMLSave can only insert XML documents that look like they were produced by OracleXMLQuery may at first sound like a drastic limitation. However, this is not the case by any stretch of the imagination. By taking advantage of an appropriate XSLT transformation, virtually any XML document can be transformed to the canonical format required by OracleXMLSave. Given an arbitrary XML document YourDoc and a database table or view into which you want to insert the document, you can create an XSLT transformation that transforms the source document YourDoc into a target document YourDocCanonical having precisely the structure needed for automatic insertion into your table or view.

Let's say that the source document is an XML news feed like the one you'd see if you were to browse the following URL:

https://www.moreover.com/cgi-local/page?index_xml+xml

A shortened version of such an XML document appears in Example 12.3.

Example 12.3. Moreover.com XML News Feed

<?xml version="1.0"?>
   <!DOCTYPE moreovernews 
      SYSTEM "https://www.moreover.com/xml/moreovernews.dtd">
   <moreovernews>
      <article id="4227581">
         <url>https://d.moreover.com/click/here.pl?x4227575</url>
         <headline_text>Austin: webMethods gets deal with Dell</headline_text>
         <source>dbusiness.com</source>
         <media_type>text</media_type>
         <cluster>XML and metadata news</cluster>
         <tagline>Austin</tagline>
         <document_url>https://washington.dbusiness.com/</document_url>
         <harvest_time>Oct 30 1999  7:08AM</harvest_time>
         <access_registration> </access_registration>
         <access_status> </access_status>
      </article>
      <article id="4202251">
         <url>https://d.moreover.com/click/here.pl?x4202247</url>
         <headline_text>IBM Brings XML To MQSeries</headline_text>
         <source>Internet Week</source>
         <media_type>text</media_type>
         <cluster>XML and metadata news</cluster>
         <tagline></tagline>
         <document_url>https://www.internetwk.com/</document_url>
         <harvest_time>Oct 28 1999  4:28PM</harvest_time>
         <access_registration> </access_registration>
         <access_status> </access_status>
      </article>
    </moreovernews>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We've seen that the general steps for inserting XML into the database are as follows:

Choose the table or view you want to use for inserting the XML information.
Create an XSL transformation that transforms the inbound document into the canonical format for this table or view.
Transform the inbound document into the canonical format for the table or view into which it will be inserted.
Insert the transformed document into your table or view with the OracleXML utility.

The Oracle XML SQL Utility works well for inserting XML documents you have in front of you in operating system files. However, if you need to have other computers post live XML information to your web site for insertion into your database, you'll need to use a slight twist on this approach.

The Oracle XSQL Servlet supports the <xsql:insert-request> action element, which you can include in any XSQL page to automate these steps:

Read a posted XML document from the HTTP request.
Transform it into the canonical format for insertion using any XSLT transformation you provide.
Insert the transformed document into the table or view of your choice.
Indicate the status of the operation by replacing the <xsql:insert-request> action element with an <xsql-status> element to show how many rows were inserted or to report an error.

Behind the scenes, the insert_request action handler makes programmatic use of the Oracle XSLT Processor to do the transformation and the Oracle XML SQL Utility to do the insert, so everything we've learned earlier applies here.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll study how the previous techniques can be applied from within your own Java programs.

We've learned so far that the Oracle XML SQL Utility can be used to insert XML datagrams into database tables and views. We used the command-line oraxsl utility to transform the XML datagram into canonical <ROWSET>/<ROW> format before feeding it to the OracleXML utility for insertion into the database. Later, we saw how a simple <xsql:insert-request> action element could be used in an XSQL page to accomplish the same thing. The XSQL Servlet is able to automate these steps since both the Oracle XSLT Processor (oraxsl) and the Oracle XML SQL Utility (OracleXML) can be used programmatically by any Java program.

The API for the Oracle XSLT Processor comprises two simple-to-use objects, XSLStylesheet and XSLProcessor , and the API for the Oracle XML SQL Utility is even simpler. The OracleXMLSave object takes care of inserting XML into the database for us. Here we'll look at an example of using these three objects in a simple Java program to insert the contents of a live Moreover.com news story datagram fetched directly over the Web into our newsstoryview.

Example 12.34 does the following to accomplish this feat:

Parses the live XML news feed from Moreover.com by calling the parse() method of a DOMParser object, passing it a string URL to the live news feed:

// Create a DOM Parser to Parse the News Document
DOMParser dp = new DOMParser( );
dp.parse( theNews );
// Parse the document at the URL specified in theURLString
XMLDocument moreoverNewsDoc = dp.getDocument( );

Searches the XML datagram retrieved to count how many articles were received by using the selectNodes() method on the XML Document object, passing the XPath expression of

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In previous chapters, we've seen a variety of ways that XML datagrams can be broken up and stored relationally. Applications can then use XML for universal data exchange and SQL for sophisticated data management and speedy queries. However, not all XML documents are pure datagrams. When applied to pure documents and datagrams with embedded document fragments, the combined XML/SQL method stores at least some XML in its original form as marked-up text. In order to utilize stored marked-up text in a query, you'll need interMedia's Text component, which adds XML document search and full-text search capabilities to SQL.

To illustrate why interMedia is needed to fully leverage XML stored in Oracle, let's work through an example, using the simple insurance claim document shown in Example 13.1.

Example 13.1. Insurance Claim Document #77804

<!-- claim77804.xml -->
<Claim>
  <Payment>1000</Payment>
  <DamageReport>
    The insured's <Vehicle Make="Volks">Beetle</Vehicle>
    broke through the guard rail and plummeted into a ravine.
    The cause was determined to be <Cause>faulty brakes</Cause>.
    Amazingly there were no casualties.
  </DamageReport>
</Claim>

This document can be broken up and stored in a table as follows:

CREATE TABLE claim (
  claimid  NUMBER PRIMARY KEY,
  payment  NUMBER,
  damagereport  CLOB
);

where the <DamageReport> fragment is stored in the damagereport column. Because it's a CLOB (Character Large Object) datatype, the damagereport column can store large text values, including entire XML documents or document fragments. We use a CLOB in this case instead of a VARCHAR2 column because a VARCHAR2 cannot exceed 4000 bytes, while a CLOB can hold up to 4 gigabytes of text.

Stored in the table, our example document would look like the sample shown in Figure 13.1.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To illustrate why interMedia is needed to fully leverage XML stored in Oracle, let's work through an example, using the simple insurance claim document shown in Example 13.1.

Example 13.1. Insurance Claim Document #77804

<!-- claim77804.xml -->
<Claim>
  <Payment>1000</Payment>
  <DamageReport>
    The insured's <Vehicle Make="Volks">Beetle</Vehicle>
    broke through the guard rail and plummeted into a ravine.
    The cause was determined to be <Cause>faulty brakes</Cause>.
    Amazingly there were no casualties.
  </DamageReport>
</Claim>

This document can be broken up and stored in a table as follows:

CREATE TABLE claim (
  claimid  NUMBER PRIMARY KEY,
  payment  NUMBER,
  damagereport  CLOB
);

where the <DamageReport> fragment is stored in the damagereport column. Because it's a CLOB (Character Large Object) datatype, the damagereport column can store large text values, including entire XML documents or document fragments. We use a CLOB in this case instead of a VARCHAR2 column because a VARCHAR2 cannot exceed 4000 bytes, while a CLOB can hold up to 4 gigabytes of text.

Stored in the table, our example document would look like the sample shown in Figure 13.1.

Figure 13.1: Insurance claim damage report stored in a CLOB column

The claim IDs and payment amounts are stored in NUMBER columns, for which Oracle provides a very rich feature set: you can index them, reference them in WHERE and ORDER BY clauses, and use them in calculations and functions. In contrast, the damagereport column is just a chunk of stored text. Oracle8i doesn't provide many native features to leverage this data other than simple pattern matching and extracting substrings by offset. How can we utilize the information that's now locked up in the damagereport column?

If the document were an XML file

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

interMedia is a family of database extensions that allows Oracle8i to more effectively manage multimedia types such as images, movies, sound clips, and documents. interMedia Text is the component of interMedia that enables searching XML documents, document fragments, and other document content. While the interMedia extensions are technically a separate product, they are included on the Oracle8i CD and can be installed and used at no additional cost. The examples in this chapter require and assume that interMedia Text has been installed in your Oracle8i Release 2 (8.1.6) database.

The main feature of interMedia Text is scalable full-text search—that is, the ability to quickly search through a huge number of documents and find those that contain a certain word or phrase, like a web search engine. Let's walk through an example, returning to our claim table. We want to do XPath contains()-like full-text searches on the damagereport column. The first step is to build a specialized index on the column:

CREATE INDEX damagereportx ON claim(damagereport) INDEXTYPE IS ctxsys.context;

interMedia Text versions 8.1.5 and 8.1.6 use PL/SQL external procedures for indexing. This means that in order for a CREATE INDEX to work, you need to have a Net8 listener running and configured to invoke external procedures. If the listener is not running or is not properly configured, the CREATE INDEX statement will fail with the error message:

DRG-50704: Net8 listener is not running or cannot start external procedures

Details on how to configure the listener.ora and tnsnames.ora files can be found in the Oracle8i Administrator's Guide (see the section "Managing Process for External Procedures") or in the interMedia 8.1.5 Technical Overview; both are available online at the Oracle Technology Network web site, https://technet.oracle.com.

The INDEXTYPE clause tells the database to build a special type of index called a context index, instead of the regular index used for other types of data. A regular index allows efficient equality and range searches. The context index, on the other hand, allows full-text searching, using the SQL CONTAINS( )

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As we've learned, interMedia by default performs case-insensitive word matching, ignoring punctuation. So the CONTAINS query:

snow

matches all the following:

snow man
Snow Day
Let it snow!

A multiple-word query is treated as a phrase search. The words must come in the specified order, with no intervening words. For instance, the query:

snow tires

matches:

Snow tires are required.
Very deep snow -- tires require chains.

but not:

Bob quickly tires -- snow shoveling is hard work.  (incorrect order)
Snow man tires are the best you can buy!           (intervening words)

The query language also offers familiar Boolean operators. The query:

snow AND tires

finds any document with the word "snow" as well as the word "tires," while:

snow OR tires

finds any document with the word "snow" or the word "tires," or both. Parentheses can be used for grouping:

(rain OR snow) AND tires

Keep in mind that this whole expression is a single argument to the SQL CONTAINS function, so a real query would look like this:

SELECT claimid
  FROM claim
 WHERE CONTAINS(damagereport, '(rain OR now) AND tires') > 0

The syntax of the WITHIN operator is fairly simple:

                  text_subquery WITHIN elementname

text_subquery can involve words, phrases, Boolean operators—pretty much anything and everything we've seen so far. element can be any XML tag. Although XML tags and XPath queries are case-sensitive, the interMedia query language is not, so all case variations of the tag are matched. In most search applications, this is actually a benefit, but if your queries must distinguish tag case, the index can use the XML sectioner—covered later in this chapter—instead of the autosectioner.

Like an XPath contains(), a WITHIN search searches the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The examples we've seen so far with the autosectioner work relatively well when all of the documents have the same doctype. This cannot always be guaranteed. Information aggregators receive data from multiple sources that usually do not share a common DTD, for instance. XSL transformation could be used to transform incoming documents to a single standard, but this approach suffers from two limitations:

It may not be possible to come up with a single standard DTD that can accommodate all expressible data in the various incoming DTDs. Even when this is possible, the process may be irreversible so that you can't get the original document back, or the standard DTD may be so broad that documents vary quite a bit.
Transformation may be an expensive process, limiting data import capacity.

In most cases like this, the various doctypes are stored in the same table, creating a document set that is heterogeneous. In such cases, the autosectioner may have difficulty searching the entire document set. For instance:

Different doctypes may use different tags to represent the same information. This will force queries to use OR-combined WITHIN clauses, which looks messy and is less efficient than single WITHIN clauses.
Different doctypes may use the same tags to represent different information, or the autosectioner's inability to distinguish tag case may lead to a tag collision. These situations will make queries less precise because the WITHIN clause will be unable to distinguish between the two different uses of the same tag.

What's needed to handle these situations in heterogeneous document collections is a more abstract concept of document structure, independent of the lexical representation of tags—essentially, a "logical tag." These logical tags are called

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As time goes on, the DTD of your document collection may change, going through several versions as it evolves. A heterogeneous collection faces that challenge, as well as the challenge of new, unanticipated doctypes entering the system. How can interMedia cope with these changes?

The autosectioner indexes every XML tag, so as soon as a new XML tag appears in your collection, you can use it in WITHIN searches. The only trouble spot to watch out for is tag collision.

The XML sectioner has a harder time handling new tags. With the XML sectioner, the user must define which tags to keep before creating the index. Every undefined tag is thrown away. As new tags appear in the document collection, they are simply thrown away as well.

When this happens, the index owner must modify the index to add the new tags to the index's list of tags to keep. This is done using an ALTER INDEX command. If our <DamageReport> fragments start to include a <Lawsuit> tag, for instance, the following command:

ALTER INDEX damagereportx REBUILD
PARAMETERS ('add zone section lawsuit tag Lawsuit')

will modify the index metadata and add the tag <Lawsuit> as the section lawsuit. Don't be scared by the ALTER INDEX . . . REBUILD syntax—the index is not really going to be dropped and recreated. This is a quick metadata change only. In fact, because the index is not dropped and recreated, this will not affect documents already in the system, although documents added after this call with the <Lawsuit> tag will be searchable. Existing documents with the <Lawsuit> tag must be manually reindexed. The easiest way to do this is to update the row's value to itself, as shown here:

UPDATE claim
   SET damagereport = damagereport
 WHERE claimid = 12345;

In general, to force an XML searching index to be brought up to date with changes made to your document table, you can issue the command:

EXEC ctx_ddl.sync_index('indexname');

You'll need the CTXSYS user to grant you the CTXAPP role in order to perform the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter covers only a small number of features available through interMedia Text. Just to whet your appetite, here are some of the broader text-searching features of interMedia not covered in this chapter:

More advanced features of the query language

The interMedia query language is extensive and supports Booleans, proximity, stem form expansion, wildcards, thesaurus operations, linguistic-based theme queries, relevance ranking and more. Most of these features can be used in conjunction with WITHIN for powerful XML searching. For instance, a stem form expansion query like:

$go WITHIN title

would find any of the following:

<TITLE>Go</TITLE>
<TITLE>Going Home</TITLE>
<TITLE>Gone With the Wind</TITLE>
<TITLE>The Englishman Who Went Up a Hill</TITLE>

Case-sensitive text and substring matching

Remember that when we contrasted the XPath contains() and the SQL CONTAINS earlier in this chapter, we noted that two aspects of XPath contains() were not mirrored in the SQL CONTAINS. Actually, with proper settings, interMedia can do case-sensitive text and substring searching; they just aren't the default behavior.

Field sections and stop sections

These are different from the zone sections covered in this chapter. Field sections are useful for small, non-overlapping sections like header information. Field sections are more efficient than zone sections for WITHIN queries on large homogenous document sets. Stop sections are used for common XML elements that are not useful for WITHIN searching. They can be added to the autosectioner to avoid indexing them, thereby saving space.

DML processing

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this chapter, we apply a combination of techniques learned in previous chapters to build an XMLLoader utility that assists in loading complex XML into multiple tables. We also explore using stylesheets that generate stylesheets for automating the creation of "insert transformations" needed to convert the structure of incoming XML into the canonical ROWSET/ROW format for insertion by the XML SQL utility or an XSQL page with an <xsql.insert.request> tag.

We saw in Chapter 12, that collection types can be used with object views to handle the automatic storage of XML datagrams containing nested, repeating information. Note that this technique works for any number of single-level nested collections, but does not work for multilevel nesting of repeating elements. This means that we can use the collection types technique to store <Department> datagrams that contain a nested set of <Employee>s. We can also use that technique to store <Department> datagrams that contain both a nested set of <Employee>s and a nested set of <QuarterlyAudit> entries. In general, any number of single-level repeating elements can be handled with collection types and object views, but if <Department>s contain repeating <Employee>s, and if <Employee>s, in turn, contain repeating <Dependent>s, then the technique no longer applies. This situation results from the limitation in Oracle8i that collection types cannot contain nested collections.

We need a different technique to handle the two or more levels of nested, repeating elements we'd see in examples of datagrams containing a <Customer> with a list of one or more open <Order>s, each of which has one or more <Item>s, as shown in Figure 14.1.

Figure 14.1: Datagrams with more than two levels of nesting

Another example might be a <Department> with a list of one or more open

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We saw in Chapter 12, that collection types can be used with object views to handle the automatic storage of XML datagrams containing nested, repeating information. Note that this technique works for any number of single-level nested collections, but does not work for multilevel nesting of repeating elements. This means that we can use the collection types technique to store <Department> datagrams that contain a nested set of <Employee>s. We can also use that technique to store <Department> datagrams that contain both a nested set of <Employee>s and a nested set of <QuarterlyAudit> entries. In general, any number of single-level repeating elements can be handled with collection types and object views, but if <Department>s contain repeating <Employee>s, and if <Employee>s, in turn, contain repeating <Dependent>s, then the technique no longer applies. This situation results from the limitation in Oracle8i that collection types cannot contain nested collections.

We need a different technique to handle the two or more levels of nested, repeating elements we'd see in examples of datagrams containing a <Customer> with a list of one or more open <Order>s, each of which has one or more <Item>s, as shown in Figure 14.1.

Figure 14.1: Datagrams with more than two levels of nesting

Another example might be a <Department> with a list of one or more open <Employee>s, each of which has one or more <Dependent>s. Let's roll up our sleeves and build an XMLLoader utility that exploits all the concepts we've learned earlier in this book but adds a little twist that enables us to handle the automatic insertion of datagrams with any number of nested levels of repeating elements into any number of tables.

We'll start with the XML datagram in Example 14.1, a variation on the <Department> and <Employee> datagram we worked with in an earlier chapter.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Since many of the XML datagrams you encounter will have been produced by formatting rows of database query results as XML, it will be quite common for the XML datagrams to have a "List of Something" format like this one:

<ListOfSomething>
  <Something>
      :
  </Something>
  <Something>
      :
  </Something>
      :
    <!-- repeating potentially thousands of times -->
      :
  <Something>
      :
  </Something>
</ListOfSomething>

In the case of our <DepartmentList> example in the previous section, we might encounter a <DepartmentList> datagram that looks like this:

<DepartmentList>
  <Department>
      :
  </Department>
  <Department>
      :
  </Department>
      :
    <!-- repeating potentially thousands of times -->
      :
  <Department>
      :
  </Department>
</DepartmentList>

If the <DepartmentList> datagram contains hundreds or thousands of <Department> elements, parsing the document into a tree of nodes in memory can become a problem. This is where our XMLDocumentSplitter class from Chapter 6, will come in handy.

Recall that XMLDocumentSplitter uses the stream-based SAXParser to process the XML datagram in question as a sequential stream of tags. It takes a divide and conquer approach to processing large XML documents that have the "List of Something" format by splitting the stream of incoming tags into a sequence of incoming documents based on the tag name of the <Something> element that repeats inside the <ListOfSomething> document element. The net effect is that all of the <Something> "sub-datagrams" are processed and the amount of memory used is only as much as a single <Something> datagram requires.

Since our MultiTableInsertHandler class implements the XMLDocumentHandler interface:

import java.net.URL;
import org.w3c.dom.Document;
public interface XMLDocumentHandler {
  void handleDocument( Document d , URL u ) throws Exception;
}

we can easily use MultiTableInsertHandler as the document handler that

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In Chapter 12, we learned how to use the <xsql:insert-request> element to get posted XML or HTML form data into our database. Recall that the <xsql:insert-request> action element has the following syntax:

<xsql:insert-request table="targettable" transform="style.xsl"/>

where it is the job of the style.xsl stylesheet to transform the inbound XML document into the canonical ROWSET/ROW format that reflects the structure of the targettable table or view. If you're like me, you tire quickly of manually creating these "insert transform" stylesheets and you start thinking of a way to automate their creation. You need look no further than the effective combination of XSQL pages and XSLT to get the data we need and XSLT to produce the "insert transform" for a given table.

Recall that the canonical XML structure required for insert is the same structure that is produced by doing a SELECT * query over the table in question. So let's build a simple XSQL page to do that SELECT * query over a table whose name is a parameter supplied in the request:

<?xml version="1.0"?>
<page connname="xmlbook" connection="{@connname}">
  <xsql:query null-indicator="yes" xmlns:xsql="urn:oracle-xsql">
  <![CDATA[
    SELECT *
      FROM {@table}
     WHERE rownum < 2
  ]]>
  </xsql:query>
</page>

The FROM clause uses the {@table} syntax to refer to an XSQL page parameter and is wrapped in by a CDATA section so that none of the characters that occur between the opening <![CDATA[ and the closing ]]> need to be quoted; for instance, the less-than sign in our WHERE clause. We use the WHERE rownum < 2 clause to make sure we only retrieve a single row from the table whose name is passed in as a parameter to the XSQL page request, since we need only a single row to understand the canonical structure. Finally, note that we've included the null-indicator="yes" attribute on <xsql:query> so that the underlying XML SQL utility produces an element for a column whose value is NULL instead of leaving it out. We'd like to make sure all columns are present in the XML fragment produced by the query. While we're at it, we've also parameterized the named

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In Chapter 8, we explored the two cornerstone features of Oracle XSQL Pages:

Assembling an XML data page using one or more parameterized <xsql:query> elements
Transforming that data page for delivery using an associated XSLT stylesheet

However, the Oracle XSQL Pages technology provides many additional features that make it a truly extensible framework for assembling, transforming, and delivering XML information of any kind. In this chapter, we'll get acquainted with its full feature set by studying some simple examples of each feature.

We'll begin with an overview of how XSQL page processing works, and proceed to explore all of the facilities available to your XSQL pages.

At the heart of the XSQL Servlet and the XSQL command-line processor is the XSQL page processor, an engine that uses the Oracle XML Parser, the Oracle XML SQL Utility, and the Oracle XSLT engine to breathe life into your XSQL page template whenever the page is requested. Figure 15.1 illustrates what happens when your web server receives a request for SomePage.xsql that contains a reference to the stylesheet SomeStyle.xsl.

Figure 15.1: Process flow for an XSQL page with a stylesheet

The XSQL page processor:

Receives a request from the XSQL Servlet to process SomePage.xsql
Parses SomePage.xsql using the Oracle XML Parser version 2 and caches it for subsequent reuse
Acquires an appropriate database connection based on the value of the connection attribute on the document element

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We'll begin with an overview of how XSQL page processing works, and proceed to explore all of the facilities available to your XSQL pages.

At the heart of the XSQL Servlet and the XSQL command-line processor is the XSQL page processor, an engine that uses the Oracle XML Parser, the Oracle XML SQL Utility, and the Oracle XSLT engine to breathe life into your XSQL page template whenever the page is requested. Figure 15.1 illustrates what happens when your web server receives a request for SomePage.xsql that contains a reference to the stylesheet SomeStyle.xsl.

Figure 15.1: Process flow for an XSQL page with a stylesheet

The XSQL page processor:

Receives a request from the XSQL Servlet to process SomePage.xsql
Parses SomePage.xsql using the Oracle XML Parser version 2 and caches it for subsequent reuse
Acquires an appropriate database connection based on the value of the connection attribute on the document element
Materializes the XML data page by replacing each XSQL action element of the form <xsql:xxxx> with the XML results returned by its built-in action handler
Parses the SomeStyle.xsl stylesheet and caches it for subsequent reuse
Transforms the data page by passing it, together with the SomeStyle.xsl stylesheet, to the Oracle XSLT Processor
Returns the resulting XML or HTML document to the requester

To make each page request more efficient, the XSQL page processor implements:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section describes additional options available to you for delivery of XML in a variety of special cases.

It is often desirable to serve the same data differently depending on what kind of program makes the request. For example, you may want to customize the XSLT-based presentation of an XSQL page to specifically optimize for the different capability Sets of the Netscape, Internet Explorer, and Lynx browsers. There are many reasons why you might want to differentiate in this way; here are a few of the most common:

Optimizing an interactive web page for Netscape 4's layer-based dynamic HTML requires coding techniques that are different from those used in optimizing for Internet Explorer's fully dynamic Document Object Model (DOM) in DHTML.
Internet Explorer 5.0 supports client-side XSLT, allowing the browser to handle the presentation of XML-based information instead of the server,
Lynx, because it's a character-mode browser, supports only a subset of HTML that it can faithfully display on character terminals.

Luckily, the XSQL page processor makes targeted transformations like this very easy. You can add any number of <?xml-stylesheet?> processing instructions to the top of your XSQL page, each of which may specify an optional media attribute, as in the following example:

<!-- Use "SiteMenu-ie5.xsl" for an IE5 browser -->
<?xml-stylesheet type="text/xsl" media="msie 5.0" href="SiteMenu-ie5.xsl"?>
<!-- Use "SiteMenu-ie.xsl" for an IE browser other than 5.0 -->
<?xml-stylesheet type="text/xsl" media="msie" href="SiteMenu-ie.xsl"?>
<!-- Use "SiteMenu-ns.xsl" for a Netscape browser -->
<?xml-stylesheet type="text/xsl" media="mozilla" href="SiteMenu-ns.xsl"?>
<!-- Use "SiteMenu-lynx.xsl" for a Lynx browser -->
<?xml-stylesheet type="text/xsl" media="lynx" href="SiteMenu-lynx.xsl"?>
<!-- Use "SiteMenu.xsl" as a fallback for any other kind of browser -->
<?xml-stylesheet type="text/xsl" href="SiteMenu.xsl"?>
<website>
   :
</website>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this chapter, we examine how Java developers can extend the universe of built-in XSQL action handlers to provide custom actions and information sources and expand the universe of built-in functions in XSLT and XPath for use in stylesheets.

Oracle XSQL Pages is an extensible framework. With very straightforward Java code, you can introduce new kinds of actions to perform virtually any kind of custom processing required by your application and to easily incorporate XML information from custom sources. In this section, we'll learn how to write custom XSQL action handlers.

As we highlighted in Chapter 15, the XSQL page processor processes an XSQL page by looking for action elements from the xsql namespace, and invoking an appropriate action element handler class to process each action. An action handler is a Java class that handles the runtime behavior of an action element in an XSQL page. Each action handler has full access to the objects it requires:

The DOM object representing the action element in the XSQL page: Allows the action handler to examine the attributes, nested elements, or text content of the action element to drive the behavior of the action
The XSQL page requesting the context object: Contains all the parameter values visible in the page, the current JDBC database connection (if any), and other resources available to the current page request
The XML result tree for the action: The root node for the tree of XML element content the action produces, if any

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle XSQL Pages is an extensible framework. With very straightforward Java code, you can introduce new kinds of actions to perform virtually any kind of custom processing required by your application and to easily incorporate XML information from custom sources. In this section, we'll learn how to write custom XSQL action handlers.

As we highlighted in Chapter 15, the XSQL page processor processes an XSQL page by looking for action elements from the xsql namespace, and invoking an appropriate action element handler class to process each action. An action handler is a Java class that handles the runtime behavior of an action element in an XSQL page. Each action handler has full access to the objects it requires:

The DOM object representing the action element in the XSQL page: Allows the action handler to examine the attributes, nested elements, or text content of the action element to drive the behavior of the action
The XSQL page requesting the context object: Contains all the parameter values visible in the page, the current JDBC database connection (if any), and other resources available to the current page request
The XML result tree for the action: The root node for the tree of XML element content the action produces, if any

Oracle XSQL Pages ships with a number of built-in action handlers whose functionality we've outlined in previous chapters. Each of the built-in actions is implemented using the same facilities available to you as a developer for building your own actions, so each of the action elements listed in Table 16.1 could have been written by you!

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll depart from simple examples designed to help understand the technology and provide a real-world example of a custom XSQL action handler that does something really useful. In Chapter 6 we built a class called JTidyConverter, which leveraged the freely available JTidy JavaBean to convert HTML into a well-formed XML document. Then we wrote some examples illustrating how basic XPath expressions in an XSLT stylesheet could easily extract interesting information like stock quotes from HTML returned by a live request against the Yahoo! Quotes web site. Here we will show a parallel example that illustrates how such useful, dynamic XML information can be adapted into the XSQL Pages framework. Example 16.5 shows the code for this LiveQuotes action handler.

As with our more straightforward examples earlier in this chapter, the LiveQuotes handler uses getAttributeAllowingParam to get the list of ticker symbols from the symbols attribute on the action element, then employs the JTidyConverter class to retrieve the dynamic HTML page delivered from the Yahoo! Quotes server and return it as an in-memory XML document. Rather than writing code to perform the XSLT transformation ourselves, we take advantage of the XSQL page processor's own XSQLStylesheetProcessor, which automatically loads, caches, and pools the YahooQuotes-to-QuoteStream.xsl stylesheet. Note that we have to call

getPageRequest( ).translateURL(
)

so that the relative URL YahooQuotes-to-QuoteStream.xsl is turned into an absolute URL. By calling the processToDocument( ) method instead of just process( ), we can get back the transformed <QuoteStream> as an in-memory XML document.

In order to add the <QuoteStream> XML as the result of our LiveQuotes handler, we perform these final steps:

Get the document element of the transformed <QuoteStream> document.
Remove it from the document that currently owns it.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

XSLT and XPath provide numerous built-in functions, many of which we've seen and all of which are listed in the handy XSLT/XPath reference available in Appendix D. In addition to these built-in functions, XSLT 1.0 defines an extension mechanism that allows user-defined functions to be written in any language. By associating an appropriate XML namespace to an external function or function library, you can use the methods and functions in that external library in your stylesheet.

Vendors are free to provide language bindings for their XSLT processor to enable users to author extension functions in those languages. The Oracle XSLT processor supports the XSLT 1.0 specification for extension functions in Java.

While building stylesheets, you may run into situations where you cannot find a built-in XSLT or XPath function to do the job, and you may already know that there's a Java class that provides the functionality you want. In these cases, you can use an existing Java class as an extension function to augment your transformation to include virtually anything you can access in Java.

To use the methods in a Java class as extension functions, you must declare a namespace for the Java class in your stylesheet. The namespace prefix can be any convenient name, but the namespace URI for the Java class must be a very specific value. To use a class named package.qualified.Classname, declare a namespace with the URI:

https://www.oracle.com/XSL/Transform/java/package.qualified.Classname

A future version of the XSLT specification may standardize the mechanism for extension functions in particular languages like Java, JavaScript, etc. In the interim, each vendor implementing XSLT 1.0 has picked their own scheme for associating a namespace URI with a Java class. Despite this, later in this chapter we illustrate a technique for building a stylesheet that uses Java extensions

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this last chapter, we'll work our way through all of the important capabilities of XSLT in the context of building some interesting Oracle XML applications. By the end of this chapter, I predict you will be an incurable XSLT fanatic as its amazing flexibility transforms you into a more productive developer.

In this section, we'll build a simple site for a web store that offers the basic functionality illustrated in Figure 17.1.

Figure 17.1: Page map of the Everything.com web site

Visitors to the site will be able to see featured items on the home page and can click the name of one of our "shops" to see a home page specific to that shop. They can search the site for a product, and whenever a product appears, they can click a link to see other products by the same manufacturer.

All product information needs to be displayed consistently across the web store. Our web design team has provided us with an HTML mockup, shown in Figure 17.2, of how each product should look on the site. The HTML file for the mockup is shown in Example 17.1.

Figure 17.2: Mockup of product display for our web store

Example 17.1. Source for HTML Mockup of Product Information

<html>
  <body>
    <center>
    <table border="0">
      <tr>
      <td valign="top">
        <b>Java in a Nutshell : A Desktop Quick Reference</b> by David Flanagan
        <br>Other products by <a href="xxxx">O'Reilly</a>
        <br>
        <table border="0"><tr>
          <td><img src="images/1565924878.gif"></td>
            <td valign="middle">
            <b>List Price:</b><strike>29.95</strike><br>
            <b>Our Price: <font color="blue">20.97</font></b><br>
            You Save: <b>8.98</b>
          </td>
        </tr></table>
      </td>
      </tr>
    </table>
    </center>
  </body>
</html>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll build a simple site for a web store that offers the basic functionality illustrated in Figure 17.1.

Figure 17.1: Page map of the Everything.com web site

Visitors to the site will be able to see featured items on the home page and can click the name of one of our "shops" to see a home page specific to that shop. They can search the site for a product, and whenever a product appears, they can click a link to see other products by the same manufacturer.

All product information needs to be displayed consistently across the web store. Our web design team has provided us with an HTML mockup, shown in Figure 17.2, of how each product should look on the site. The HTML file for the mockup is shown in Example 17.1.

Figure 17.2: Mockup of product display for our web store

Example 17.1. Source for HTML Mockup of Product Information

<html>
  <body>
    <center>
    <table border="0">
      <tr>
      <td valign="top">
        <b>Java in a Nutshell : A Desktop Quick Reference</b> by David Flanagan
        <br>Other products by <a href="xxxx">O'Reilly</a>
        <br>
        <table border="0"><tr>
          <td><img src="images/1565924878.gif"></td>
            <td valign="middle">
            <b>List Price:</b><strike>29.95</strike><br>
            <b>Our Price: <font color="blue">20.97</font></b><br>
            You Save: <b>8.98</b>
          </td>
        </tr></table>
      </td>
      </tr>
    </table>
    </center>
  </body>
</html>

We'll use Dave Raggett's Tidy tool (described in Chapter 6) to convert the web designer's HTML source code into an indented, well-formed XML file that will evolve into an XSLT stylesheet that produces the same look and feel. Run Tidy as follows:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll build our second application—a dynamic, database-driven news portal that derives its content from user preference information.

Web-based applications frequently present information to the user in pages. Just consider some sites you might be familiar with. If you perform a search at Amazon.com, WilliamsSonoma.com, or Google.com, you will see your search results a page at a time with a little paging widget that shows you where you are. As Figure 17.11 illustrates, each site has a slightly different look to its paging widget, but each one offers the same functionality.

Figure 17.11: Example paging widgets

All of these sites present the results of querying large, relational databases. However, to keep the amount of information on any given page manageable and to keep your response time fast, they all present the results incrementally in pages. A naive technique for scrolling through database query results like this is to open a database cursor, fetch ten records, and keep the cursor open so when the user clicks on the "next page" icon you can fetch ten more pages. However, this technique has many drawbacks:

Generally, database cursors can only be scrolled forward, so offering a facility to go back to the previous page would be tricky.
Scrolling backwards or maintaining a current record position in a cursor requires keeping the database cursor open across web page requests. This means you must dedicate a database connection to every single visitor to your site. "Holy maximum-number-of-sessions-exceeded, Batman!" Twenty thousand users querying for web sites, books, or pots and pans would need 20,000 database connections to keep each of their cursors open. And what if users never click on the "next page" button before zooming off to another site to check their stocks? In this case, when do you close the cursor?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Now we're going to shift into high gear and implement an online discussion forum application.

Online discussion forums are a popular way to post questions on various topics and have peers answer your questions. Figure 17.26 illustrates the basic page map of the discussion application.

Figure 17.26: Page map for an online discussion forum

Users start by looking at a list of discussion forums organized around interesting areas like Java, XML, Oracle8i, etc. From there, they can drill into a particular forum to see what topics users are discussing; they can get an overview of all active topics across all the forums; or they can search for a particular word or phrase and browse through the results. While in a particular forum, users can browse the topics as well as drill in to see what replies have been posted for any topic. In addition, if a user logs in by providing a user ID, they can post new topics and post replies to existing topics.

We'll use this example to illustrate the maximum separation of data from presentation, and the maximum abstraction of our web pages' structure from their appearance. Let's start with the database part of the application. We can keep track of the forums, forum topics, topic postings and replies, and forum users with a straightforward database schema like the one shown in Figure 17.27.

Figure 17.27: Database schema for discussion forum application

First we create the forum_user table to store our users:

CREATE TABLE forum_user(
  userid     VARCHAR2(10),
  email      VARCHAR2(80),
  CONSTRAINT forum_user_pk PRIMARY KEY (userid)
);

Next, we create the forum table for storing the forums themselves:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This appendix includes the PL/SQL package implementations for the XML helper packages we used back in Chapter 5:

xml
xmldoc
xpath
xslt
http

This appendix also contains instructions on how to install these packages in your Oracle8i database.

Section A.1: Installing the XML Helper Packages

Section A.2: Source Code for the XML Helper Packages

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To install all of the XML helper packages we used in Chapter 5, follow these steps:

Change directory to the ./examples/appa directory, which contains the files for this appendix.
Connect as SYS using SQL*Plus and run the install_sys_helper_packages.sql:
```
sqlplus sys/change_on_install @install_sys_helper_packages.sql
```

Compile PropertyHelper.java and use loadjava to load it into the database as SYS:

javac PropertyHelper.java
loadjava -verbose -resolve -user sys/change_on_install PropertyHelper.class

Connect as XMLBOOK using SQL*Plus and run the install_helper_packages.sql script:
```
sqlplus xmlbook/xmlbook @install_helper_packages.sql:
```

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The xml, xmldoc, xpath, and xslt packages depend on having the Oracle XML Parser for PL/SQL installed. (See Chapter 5 for installation details). In addition, since the Oracle XML Parser for PL/SQL wraps the Oracle XML Parser for Java, one of the following must be true before you can successfully compile and run these packages:

You must have already:
- Installed the Oracle XML Parser for Java in your schema
- Installed the Oracle XML Parser for PL/SQL packages in your schema, and
- Been granted the JAVAUSERPRIV privilege to execute Java code inside Oracle8i
Alternatively, you must have been granted EXECUTE permission on the Oracle XML Parser for PL/SQL packages installed in another schema.

The xml package provides key functionality for parsing XML documents from VARCHAR2, CLOB, BFILE, and URL sources. It uses the Oracle XML Parser for PL/SQL's xmlparser package. To use these routines, perform these steps:

Call one of the xml.parse functions to parse your XML document
Work with the returned xmldom.DOMDocument
Call xml.freeDocument( yourDoc ) to free the memory used by the parsed representation of your XML document

It is safe to cache the instances of xmldom.DOMDocument in PL/SQL package-level variables for parsed XML documents you need to use over and over during the same database session, but remember that the memory they occupy is not freed until you explicitly call

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This appendix describes how to install the Oracle XSQL Servlet. This servlet can be used with any servlet engine that supports mapping file extensions to a servlet class.

Installing the XSQL Servlet requires just two key steps:

Ensure that the following five archives are added properly to your servlet engine's CLASSPATH:
- oraclexsql.jar
- oraclexmlsql.jar
- xmlparserv2.jar
- classes111.zip
- < directory containing XSQL Config.xml file >
Map the .xsql file extension to the oracle.xml.xsql.XSQLServlet servlet class.

Any requests made to your web server with the .xsql file extension will then be processed by the XSQL Servlet. Note that the .xsql file extension is purely a convention. You can map any file extension (or several different extensions) to the Servlet class without problems.

Section B.1: Supported Configurations

Section B.2: Prerequisites

Section B.3: Downloading and Installing the XSQL Servlet

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The XSQL Servlet is designed to run on any Java virtual machine, using any JDBC driver, against any database. It has been tested against the most popular configurations. In this section, we document the supported configurations.

XSQL Pages and the XSQL Servlet have been tested using:

JDK 1.1.8
JDK 1.2.2

Numerous users have reported problems using XSQL Pages and the XSQL Servlet with JDK 1.1.7, which suffers problems in its character set conversion routines for UTF-8 that make it unusable for processing XSQL Pages.

This XSQL Servlet has been tested with the following servlet engines:

Oracle Internet Application Server 1.0
Allaire JRun 2.3.3
Apache 1.3.9 with JServ 1.0 and 1.1
Apache 1.3.9 with Tomcat 3.1 Servlet Engine
Apache Tomcat 3.1 Web Server + Servlet Engine
Caucho Resin 1.1
NewAtlanta ServletExec 2.2 for IIS/PWS 4.0
Oracle8i Lite Web-to-go Server
Oracle Servlet Engine in Oracle8i 8.1.7
Sun JavaServer™ Web Development Kit ( JSWDK) 1.0.1 Web Server

JavaServer Pages can use <jsp:forward> and/or <jsp:include> to collaborate with XSQL Pages as part of an application. The following JSP platforms have been tested:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle XSQL Pages depends upon:

Oracle XML Parser V2 Release 2.0.2.7
Oracle XML SQL Utility for Java
A web server that supports Java servlets
A JDBC driver, like Oracle JDBC or Oracle8i Lite JDBC

For your convenience, all of these dependent libraries are included with the XSQL Servlet distribution, and all are included with JDeveloper 3.1 on the accompanying CD-ROM.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section describes in detail how to obtain and install the XSQL Servlet.

You can download the XSQL Servlet distribution by following these steps:

Visit https://technet.oracle.com/tech/xml/xsql_servlet/.
Click on the Software icon at the top of the page.
Log in with your OTN username and password; registration is free if you do not already have an account.
Select whether you want the Windows NT or Unix download; both contain the same files.
Acknowledge the licensing agreement and download the survey.
Click on xsqlservlet_v1_0_0_0.tar.gz or xsqlservlet_v1_0_0_0.zip. Note that if a later version is available, the filenames will reflect the later version number.

To extract the contents of the XSQL Servlet distribution, do the following:

Choose a directory under which you would like the .\xsql directory and subdirectories to go (e.g., C:\).
Change directory to C:\, then extract the downloaded XSQL archive file. For example, on Unix:
```
tar xvfz xsqlservlet_v1_0_0_0.tar.gz
```
or on Windows:
```
pkzip25 -extract -directories xsqlservlet_v1_0_0_0.zip
```
using the pkzip25 (see https://www.pkware.com/shareware/pkzip_cli.html) command-line tool or the WinZip (see https://www.winzip.com) visual archive extraction tool.
If you use Windows and will be using the Web-to-go server, start with the next section. If you use Unix or if you use Windows with a different web server (not Web-to-go), skip ahead to Section 2.3.4.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Figure 3.1 is a complete conceptual map showing how all of the key XML-related standards discussed in this book fit together.

Figure C.1:Conceptual map to the XML family

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This appendix includes compact quick references for XSLT, XPath, and XML. Mulberry Technologies has been distributing these quickrefs for several years, and I use them often. I'm grateful to Mulberry for allowing us to include them in this book. The latest versions are available online at https://www.mulberrytech.com/quickref/.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Table of Contents