Using XML

Oracle9i has adapted XML to the database much as Oracle8 did with Java. XML is a HTML like language that is very useful in formatting data for all kinds of environments such as the Web and wireless devices. Oracle's implementation of XML is easy to use and can easily be incorporated with other languages such as PL/SQL, Java, and C++. Oracle has also used their own technology to create another Web page-generating facility called XSQL pages. These XSQL pages are simple to use and allow developers to easily get started with XML.

This final section will build a Web site using XML and then will adapt the same example for use with a cellular phone.

XSQL pages make use of the xsql servlet that Oracle developed. This servlet enables the user to connect to an Oracle database (actually any JDBC accessible database will do) and take the results of SQL queries and turn them into well-formed XML documents. This XML document can then be translated using XML Stylsheets (XSLT). These style sheets in turn control how the output will appear and on what kind of device the output is intended for. The servlet can also be used from the computer command line or from a java program to emit structured (or unstructured) data in any form.

Listing 11.10 is a simple query that produces an XML document that contains all the cars in our inventory. Figure 11.12 shows the SQL*Plus output of the SQL query and Figure 11.13 illustrates what the output looks like with the command line syntax: xsql.bat inventory.xsql. The output from the servlet is the same as the output from SQL*Plus but it has been transformed into a well-formed XML document. The output data is enclosed in <ROWSET><ROW></ROW></ROWSET> tags. The individual data values are further delimited by tags named after their column name.

Listing 11.10. Simple XML Program—inventory.xsql

1: <?XML version='1.0'?> 2: <xsql:query XMLns:xsql="urn:oracle-xsql" connection="scott"> 3: SELECT inv_id 4: , auto_description 5: , sale_photo_location 6: , sale_amt 7: FROM st_cars_for_sale 8: ORDER BY inv_id 9: 10: </xsql:query>

#
# Associate .xsql extension to XSQL Servlet
ApJServAction .xsql /servlets/oracle.xml.xsql.XSQLServlet
#

Because XSQL pages are themselves XML documents they must be well formed and follow the rules for any XML document. In Listing 11.10, the XML tag <? ?> tells the parser to follow version 1 XML standards. The second line defines the namespace of the document to be urn:oracle-xsql. If this namespace is not named exactly then the servlet will not understand what to do with the tags and will most likely emit nothing. The attribute connection="scott" indicates a named database connection to use.

This named connection is defined in the $ORACLE_HOME/xdk/lib/ XSQLConfig.xml file (see Listing 11.11). This file contains a number of parameters that the XSQL servlet uses to define its operating parameters. In the <connectiondefs> tag there are a number of “example” connections. Define one with the username, password, jdbc driver url, and the name of the database driver for your examples.

Listing 11.11. XSQLConfig.xml File

<connectiondefs> <connection name="demo"> <username>scott</username> <password>tiger</password> <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl> <driver>oracle.jdbc.driver.OracleDriver</driver> </connection> ... <connection name="scott"> <username>scott</username> <password>tiger</password> <dburl>jdbc:oracle:thin:@localhost:1521:dto</dburl> <driver>oracle.jdbc.driver.OracleDriver</driver> </connection> </connectiondefs>

In Listing 11.10, lines 3 through 9 are simply the SQL query. The query can be as simple or complex as need be and you will learn later how to use parameters. Other things it can also do is execute stored procedures, insert/update/delete data, return only the first N rows, return rows x-y, and generally do most anything (with user defined actions—see Oracle XDK documentation complete details). Line 10 is the closing tag to the XML page.

Any query that is executed using this XML servlet will return the same ROWSET-ROW tag combination. Do not think of this as a limitation but do think of it as a standardization of output. Now SML stylesheets can be applied to the SQL page and turn the data into any format desired.

The first thing to notice is that XML looks a whole lot like HTML. HTML is not nearly as strict about closing tags and the browser manufacturers quickly adapted and made their browsers less strict in how they parsed the HTML.

XML is very unforgiving in that all tags are case sensitive and all tags must have ending tags. Unfortunately the programs that generated the page1.html (Listing 11.3) and page2.html (Listing 11.5) are less-than-perfect and many of the closing tags are missing. These will need to be fixed before proceeding to XML.

Before starting on a XML document with a XSLT stylesheet, the page1.html and page2.html need to be valid XML documents. We need our template (page1.html, page2.html) to be a valid XML document.

Page1.html will be the template for the transformation. It has been renamed to page1.xsl and has some minor changes made to it, see Listing 11.12.

Listing 11.12. Page1.xsl

1: <html xsl:version="1.0" XMLns:xsl="http://www.w3.org/1999/XSL/Transform"> 2: <head> 3: <title>Metro Motors</title> 4: <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/> 5: </head> 6: <body bgcolor="#CCCCCC" text="#990000"> 7: <xsl:for-each select="RESULTS/CAR"> 8: <p><img src="/mmimage/logo.gif" width="150" height="100" align="middle"/> 9: <b><font face="Geneva, Arial, Helvetica, san-serif"> 10: <font size="4">Metro Motors Web Site 265-4774</font></font></b></p> 11: <table width="100%" border="0"> 12: <tr> 13: <td rowspan="3" width="46%"> 14: <img src="/mmimage{IMAGE}" width="300" height="250"/></td> 15: <td width="54%" height="83"> 16: <font face="Geneva, Arial, Helvetica, san-serif"><b>Description:</b> 17: <xsl:value-of select="DESCRIPTION"/></font></td> 18: </tr> 19: <tr> 20: <td width="54%" height="39"> 21: <font face="Geneva, Arial, Helvetica, san-serif"><b>Suggested Price:</b> 22: <xsl:value-of select="AMOUNT"/></font></td> 23: </tr> 24: <tr> 25: <td align="center" width="54%"> 26: <a href="page1.xsql?id={ID+ (-1)}"> 27: <img src="/mmimage/prev.gif" width="100" height="30" border="0" alt="Previous"/> 28: </a> 29: <a href="page1.xsql?id={ID+1}"> 30: <img src="/mmimage/next.gif" width="100" height="30" border="0" alt="Next"/> 31: </a> 32: <a href="page2.xsql?id={ID}"> 33: <img src="/mmimage/buy.gif" width="100" height="30" 34: border="0" alt="Buy This Car"/> 35: </a> 36: </td> 37: </tr> 38: </table> 39: </xsl:for-each> 40: </body> 41: </html>

Line 1 starts off with the HTML tag. It has a few attributes that are not found in most HTML pages, such as the version of XSL being used and the namespace for the HTML pages.

Line 4 contains some meta data being sent with the header of the HTML page. This tells the browser to treat the results of this page as mime type text/html and also tells the browser which character set to use.

Line 7 introduces a new XSLT tag—the for-each tag. The for-each tag is basically the looping construct that XSLT uses to search through the raw XML document. The for-each is not wide open so a search path needs to be supplied for it to search for elements. Otherwise it will just take the 1 instance of the root document. In this example all the tags under RESULTS/ CAR tags are desired.

Lines 14 and 17 show how to pull dynamic XML data from the XML query and put it into this template. The first method illustrated by line 14 is where you are inside quoted text, {tagname}. Line 17 shows how to pull information for a regular text node.

Line 22 selects the suggested price (AMOUNT) from the XML query document. Lines 26, 29, and 32 again show use of XPATH expressions to pull data from inside an attribute. The XPATH language allows us to perform mathematical operations on the result before emitting the final result. Notice the href setting up the previous (line 26) and next (line 29) buttons and the page2.xsql reference on line 32. This is setting up the link to the previous and next car in the inventory and allowing the user to navigate to the offer page (page2.xsql).

These are all the changes that are required to make the page1.xsl stylesheet.

The data page that generates this XML document is illustrated in Listing 11.13, data.xsql.

Listing 11.13. Data.xsql

1: <?XML version="1.0" encoding='ISO-8859-1'?> 2: 3: <RESULTS XMLns:xsql="urn:oracle-xsql" connection="scott" id="1"> 4: <xsql:include-owa> 5: get_car_XML({@id}); 6: </xsql:include-owa> 7: 8: </RESULTS>

Line 3 sets up the XSQL namespace and tells Oracle to use the “scott” connection. The attribute id=“1” sets up a default value for the id parameter. If a value for ID is passed in then it overrides the default but if nothing is passed in then ID will take the default value of 1 and passes it to the function get_car_XML on line 5.

Line 4 shows the use of the include-owa action element. The include-owa element allows the use of PL/SQL skills inside a procedure to produce any arbitrary XML document. The include-owa element can be used any time that the desired results cannot be created from a query or queries. The get_car_XML (see the listing in Appendix B) procedure makes use of the get_car PL/SQL procedure used with the PSP earlier in this chapter. XML is used to create an XML page that looks like Listing 11.14.

Listing 11.14. Output from Data.xsl (Listing 11.14)

<RESULTS> <CAR> <ID>1</ID> <DESCRIPTION>1995 Toyota Camry</DESCRIPTION> <IMAGE>/camry_after.jpg</IMAGE> <AMOUNT>9999</AMOUNT> </CAR> </RESULTS>

Now all that is needed is to combine this data (Listing 11.14) with the stylesheet. Listing 11.15 brings all the pieces together as page1.xsql.

Notice that the page1.xsl is referenced in line 2 and that the data.xsql is referenced in line 3. Figure 11.14 illustrates the output results from Page1.xsql. Notice the URL line, calling the Page1.xsql directly.

Listing 11.15. Page1.xsql

1: <?XML version="1.0" encoding='ISO-8859-1'?> 2: <?XML-stylesheet type="text/xsl" href="page1.xsl"?> 3: <xsql:include-xsql href="data.xsql" XMLns:xsql="urn:oracle-xsql"/>

Use the same method from page1.xsql for page2.xsql. First, create the data2.xsql as illustrated in Listing 11.16.

Listing 11.16. data2.xsql

1: <?XML version="1.0" encoding='ISO-8859-1'?> 2: <RESULTS XMLns:xsql="urn:oracle-xsql" connection="scott" id="1"> 3: 4: <xsql:include-owa> 5: get_car_XML({@id}); 6: </xsql:include-owa> 7: 8: <xsql:include-owa> 9: make_offer_XML( 10: {@id} 11: , '{@buyer}' 12: , '{@phone}' 13: , '{@offer}' 14: ); 15: </xsql:include-owa> 16: 17: </RESULTS>

Listing 11.16 looks very similar to data.xsql, Listing 11.13. The only difference is that data2.xsql is passing parameters to the make_offer_XML procedure (see Appendix B for the make_offer_XML listing).

Lines 5, 10, 11, 12, and 13 use the XPATH expressions: {@}. These expressions are how parameters are used. Notice that on line 2 the only default parameter is id=1. All the other parameters must either be passed in to the page or NULL will be passed. The output XML page from data2.xsql looks like Listing 11.17.

Line 6 is another xsql action element that directs the page to include all the requested parameters into the page. It creates the tag illustrated in Listing 11.18.

Listing 11.17. Output from Data2.xsql

<PAGE> <RESULTS id="1"> <CAR> <ID>1</ID> <DESCRIPTION>1995 Toyota Camry</DESCRIPTION> <IMAGE>/camry_after.jpg</IMAGE> <AMOUNT>$9,999.00</AMOUNT> </CAR> <OFFER /> </RESULTS> <request> <id>1</id> <parameters /> <session /> <cookies /> </request> </PAGE>

Now this data page needs to be transformed into HTML with the Page2.xsl stylesheet that was modified from the original Page2.html (back in Listing 11.5). Listing 11.18 illustrates the Page2.xsl stylesheet.

Listing 11.18. Page2.xsl

1: <html xsl:version="1.0" XMLns:xsl="http://www.w3.org/1999/XSL/Transform"> 2: <head> 3: <title>Metro Motors - Make An Offer</title> 4: <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/> 5: </head> 6: <xsl:variable name="updateresult" select="PAGE/RESULTS/OFFER"/> 7: <xsl:variable name="buyer" select="PAGE/request/parameters/buyer"/> 8: <xsl:variable name="phone" select="PAGE/request/parameters/phone"/> 9: <xsl:variable name="offer" select="PAGE/request/parameters/offer"/> 10: <body bgcolor="#CCCCCC" text="#990000"> 11: <xsl:for-each select="PAGE/RESULTS/CAR"> 12: <p><img src="/mmimage/logo.gif" width="150" height="100" align="middle"/> 13: <b><font face="Geneva, Arial, Helvetica, san-serif"> 14: <font size="4">Metro Motors Web Site 265-4774</font></font></b></p> 15: <form name="form2" method="post" action="page2.xsql"> 16: <input type="hidden" name="id" value="{ID}"/> 17: <table width="100%" border="0"> 18: <tr> 19: <td rowspan="4" width="46%"><a href="page2.html"> 20: <img src="/mmimage{IMAGE}" width="300" height="250" 21: border="0" alt="Image Not Available"/> 22: </a> 23: </td> 24: <td width="54%" height="83"><font face="Geneva, Arial, 25: Helvetica, san-serif"> 26: <b>Description:</b> 27: <xsl:value-of select="DESCRIPTION"/></font></td> 28: </tr> 29: <tr> 30: <td width="54%" height="39"><font 31: face="Geneva, Arial, Helvetica, san-serif"><b>Suggested Price:</b> 32: <xsl:value-of select="AMOUNT"/></font></td> 33: </tr> 34: <tr> 35: <td> 36: <p align="CENTER"><b><font 37: face="Geneva, Arial, Helvetica, san-serif" COLOR="Fuchsia"> 38: <font size="3"> 39: <xsl:value-of select="$updateresult"/></font></font></b> 40: </p> 41: <table width="100%" border="0"> 42: <tr> 43: <td width="33%"> 44: <div align="right"> 45: <font face="Geneva, Arial, Helvetica, san-serif"><b>Buyer 46: Name:</b></font></div> 47: </td> 48: <td width="67%"> 49: <input type="text" name="buyer" 50: maxlength="50" value="{$buyer}"/> 51: </td> 52: </tr> 53: <tr> 54: <td width="33%"> 55: <div align="right"> 56: <font face="Geneva, Arial, Helvetica, san-serif"><b>Buyer 57: Phone:</b></font></div> 58: </td> 59: <td width="67%"> 60: <input type="text" name="phone" 61: maxlength="15" value="{$phone}"/> 62: </td> 63: </tr> 64: <tr> 65: <td width="33%"> 66: <div align="right"> 67: <font face="Geneva, Arial, Helvetica, san-serif"> 68: <b>Offer:</b></font></div> 69: </td> 70: <td width="67%"> 71: <input type="text" name="offer" 72: maxlength="15" value="{$offer}"/> 73: </td> 74: </tr> 75: </table> 76: </td> 77: </tr> 78: <tr> 79: <td align="center" width="54%"> 80: <input type="image" border="0" 81: src="/mmimage/offer.gif" width="100" height="30" alt="Place Offer" > 82: <a href="page1.xsql?id={ID}"> 83: <img src="/mmimage/browse.gif" width="100" 84: height="30" border="0" alt="Browse Cars"/></a> 85: </input> 86: </td> 87: </tr> 88: </table> 89: </form> 90 <p>&nbsp;</p> 91: </xsl:for-each> 92: </body> 93: </html>

Lines 6 through 9 create and use stylesheet variables. They use XPATH queries against the data page to populate the variables.

Line 39 then uses one of these variables in the XPATH. Variables are referenced by using the $ prefix.

Lines 50, 61, and 72 use the input parameters as default values for the text boxes. Notice how the input length from the user is controlled.

The Page2.XSQL (see Listing 11.19) brings all the pieces together and produces the output in Figure 11.15.

Listing 11.19. Page2.xsql

1: <?XML version="1.0" encoding='ISO-8859-1'?> 2: <?XML-stylesheet type="text/xsl" href="page2.xsl"?> 4: <PAGE XMLns:xsql="urn:oracle-xsql"> 5: <xsql:include-xsql href="data2.xsql"/> 6: <xsql:include-request-params/> 7: </PAGE>

XML for Wireless Devices

Part of the promise of XML is that it can easily transform data from one format to another. The remainder of this chapter shows how to display this information upon a cell phone or a PDA (personal data assistant) using WML and WML stylesheets.

The XSQL solution has proven to successfully separate the data from the presentation of the data. All that is needed now is a different stylesheet, one that creates WML presentation (for wireless devices) instead of HTML presentation.

For this example, the WML examples for page1 and page2 will be page1w.xsl and page2w.xsl respectively. All that is needed now are new WML stylesheets and a small modification to the page1.xsql and page2.xsql scripts.

All we need to do to support our WML phones is create our stylesheets and make a modification to our driving page1.xsql (see Listing 11.20) and page2.xsql (see Listing 11.21).

Listing 11.20. Page1.xsql Modified for WML

1: <?XML version="1.0" encoding='ISO-8859-1'?> 2: <?XML-stylesheet type="text/xsl" href="page1.xsl" media="MSIE"?> 3: <?XML-stylesheet type="text/xsl" href="page1w.xsl" media="UP"?> 4: <xsql:include-xsql href="data.xsql" XMLns:xsql="urn:oracle-xsql"/>

Notice that the only differences are on line 2 and line 3 where the stylesheet is declared. Notice the media="MSIE" on line 2 and media="UP" on line 4. When a browser makes a request it sends a user-agent string as part of the header. XSQL pages compare the user-agent string with the media value. If the media value is found in the string then that stylesheet is used. Line 2 says that if the user-agent string contains MSIE then use the page1.xsl stylesheet.

Listing 11.21. Page2.xsql Modified for WML

<?XML version="1.0" encoding='ISO-8859-1'?> <PAGE XMLns:xsql="urn:oracle-xsql"> <?XML-stylesheet type="text/xsl" href="page2.xsl" media="MSIE"?> <?XML-stylesheet type="text/xsl" href="page2w.xsl" media="UP"?> <xsql:include-xsql href="data2.xsql"/> <xsql:include-request-params/> </PAGE>

The phone that has been chosen to support this example is from www.phone.com (openwave system sdk) and it sends UP as part of its user-agent string so the cell phone will use the page1w.xsl stylesheet.

Now all that is left to support WML is to come up with appropriate stylesheets. Cell phones cannot handle the graphics and colors (yet anyway) as in html. These types of items are removed and the page1.xsl is formatted to WAP/WML standards. The resulting page1w.xsl stylesheet is illustrated in Listing 11.22, page2w.xsl stylesheet is illustrated in Listing 11.23.

Listing 11.22. Page1w.sxl WML Stylesheet

<xsl:stylesheet XMLns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:output method="XML" doctype-public="-//WAPFORUM//DTD WML 1.1//EN" media- type= "text/vnd.wap.wml" doctype-system="http://www.wapforum.org/DTD/ wml_1.1.xml" /> <xsl:template match="/"> <wml> <card id="page1" title="Metro Motors Cars"> <p><b>Metro Motors:</b></p> <xsl:for-each select="RESULTS/CAR"> <p><xsl:value-of select="DESCRIPTION"/></p> <p>Price:$<xsl:value-of select="AMOUNT"/></p> <p align="center"> <anchor><go><xsl:attribute name="href"> page1.xsql?id=<xsl:value-of select="ID - 1"/></xsl:attribute> </go> Prev </anchor> <anchor><go><xsl:attribute name="href"> page1.xsql?id=<xsl:value-of select="ID + 1"/></xsl:attribute> </go> Next</anchor> </p> <p align="center"> <anchor><go><xsl:attribute name="href"> page2.xsql?id=<xsl:value-of select="ID + 0"/></xsl:attribute> </go> Make Offer</anchor> </p> </xsl:for-each> </card> </wml> </xsl:template> </xsl:stylesheet>

Listing 11.23. Page2w.sxl WML Stylesheet

<xsl:stylesheet XMLns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:output method="XML" doctype-public="-//WAPFORUM//DTD WML 1.1//EN" media-type= "text/vnd.wap.wml" doctype-system="http://www.wapforum.org/DTD/wml_1.1.xml" /> <xsl:template match="/"> <wml> <xsl:variable name="ID" select="PAGE/request/parameters/id"/> <xsl:variable name="buyer" select="PAGE/request/parameters/buyer"/> <xsl:variable name="phone" select="PAGE/request/parameters/phone"/> <xsl:variable name="offer" select="PAGE/request/parameters/offer"/> <card> <p><b>Metro Motors Offer:</b></p> <p><b><xsl:value-of select="PAGE/RESULTS/OFFER"/></b></p> <xsl:for-each select="PAGE/RESULTS/CAR"> <p><xsl:value-of select="DESCRIPTION"/></p> <p>Price:$<xsl:value-of select="AMOUNT"/></p> <p> Buyer: <input name="buyer" size="10"/><br/> Phone: <input name="phone" size="10"/><br/> Offer: <input name="offer" size="10" format="*N"/><br/> </p> <p align="center"> <anchor><go><xsl:attribute name="href"> page1.xsql?id=<xsl:value-of select="ID + 0"/></xsl:attribute> </go> Browse </anchor> <anchor> <go href="page2.xsql" method="post"> <postfield name="id" value="{$ID}" /> <postfield name="buyer" value="$(buyer)" /> <postfield name="phone" value="$(phone)" /> <postfield name="offer" value="$(offer)" /> </go> Make Offer</anchor> </p> </xsl:for-each> </card> </wml> </xsl:template> </xsl:stylesheet>

WML documents must be valid XML documents and have special tags of their own that don't conform to the HTML standard. Discussing WML is beyond the scope of this book. Additional WML information is available at www.phone.com.

The results of running these pages through the www.phone.com cellular phone emulator can be seen in Figures 11.16 and 11.17.

Oracle XSQL pages are very powerful. XSQL allows you to truly separate data from presentation. XSLT and XPATH make translating data into any format easy. XML can be manipulated through the XSQL servlet, Java, PL/SQL, and any combination thereof. This section is intended to give you a functioning example, once again, entire books are devoted to XML including “XML by Example,” ISBN: 0-7897-2504-5, also published by Que.