Using XML in Oracle Database Applications Part 2: About Oracle XML Products  November 1999 (Updated June 2001)

Oracle XDK

XML Parsers

XSL Transformation Support

Namespace Support

Validating and Non-Validating Mode Support

About DOM and SAX APIs

The following figure shows an XML document and corresponding SAX and DOM representations.
 

XML Document	SAX Events	DOM Tree
<?xml version="1.0"?>   <EMPLIST>     <EMP>      <ENAME>MARTIN</ENAME>     </EMP>     <EMP>      <ENAME>SCOTT</ENAME>     </EMP>   </EMPLIST>	start document start element: EMPLIST start element: EMP start element: ENAME characters: MARTIN end element: EMP start element: EMP start element: ENAME characters: SCOTT end element: EMP  end element: EMPLIST end document

Example: Java XML Parser

import org.w3c.dom.*;

import java.util.*;
import java.io.*;
import java.net.*;


import oracle.xml.parser.v2.*;

public class XSLTransform 
{
   public static void main (String args[]) throws Exception
   {
      DOMParser parser;

      XMLDocument xml, xsldoc, out;

      URL xslURL;
      URL xmlURL;

      try 
      {

         if (args.length != 2) 
         {
            // Pass in the names of the XSL and XML files

            System.err.println("Usage: java XSLTransform 
                xslfile xmlfile");
            System.exit(1);
         }

         // Parse XSL and XML documents
         parser = new DOMParser();
         parser.setPreserveWhitespace(true);
         
         xslURL = createURL(args[0]);
         parser.parse(xslURL);
         xsldoc = parser.getDocument();
         
         xmlURL = createURL(args[1]);
         parser.parse(xmlURL);
         xml = parser.getDocument();

         // Instantiate the stylesheet
         XSLStylesheet xsl = new XSLStylesheet(xsldoc, xslURL);

         XSLProcessor processor = new XSLProcessor();


         // Display any warnings that may occur
         processor.showWarnings(true);
         processor.setErrorStream(System.err);

         // Process XSL
         DocumentFragment result = processor.processXSL(xsl, xml);

         // Create an output document to hold the result
         out = new XMLDocument();

         // Create a dummy document element for the output document
         Element root = out.createElement("root");
         out.appendChild(root);

         // Append the transformed tree to the dummy document element
         root.appendChild(result);
         
         // Print the transformed document
         out.print(System.out);
      }
      catch (Exception e)

      {
         e.printStackTrace();
      }
   }
}

XML Class Generator for Java

Example: XML Class Generator For Java

The Input DTD

<!-- DTD for Employee Data --> 
<!ELEMENT EMP (EMP_ROW)*> 
<!ELEMENT EMP_ROW (EMPNO, ENAME?, JOB?, MGR?, HIREDATE?, SAL?,
                   COMM?, DEPTNO?)> 
<!ATTLIST EMP_ROW ROWNO CDATA #REQUIRED> 
<!ELEMENT EMPNO (#PCDATA)> 
<!ELEMENT ENAME (#PCDATA)> 

<!ELEMENT JOB (#PCDATA)> 
<!ELEMENT MGR (#PCDATA)> 
<!ELEMENT HIREDATE (#PCDATA)> 
<!ELEMENT SAL (#PCDATA)> 
<!ELEMENT COMM (#PCDATA)> 
<!ELEMENT DEPTNO (#PCDATA)>

Processing the DTD to Generate Java Classes

import java.io.*;
import java.net.*;
import oracle.xml.parser.*;
import oracle.xml.classgen.*;
import org.w3c.dom.Element;

public class SampleMain
{
  public SampleMain()
  {

  }
  public static void main (String args[]) 
  {
     // validate arguments
    if (args.length < 1) 
    {
      System.out.println("Usage: java SampleMain "+
                   "[-root <rootName>] <fileName>");
      System.out.println("fileName\t  Input file, XML document or " +
                   "external DTD file");
      System.out.println("-root <rootName>  Name of the root Element " +
                   "(required if the input file is an external DTD)");
      return ;
    }
    try // to open the External DTD File
    { 
      // instantiate the parser
      XMLParser parser = new XMLParser();

      if (args.length == 3)
        parser.parseDTD(fileToURL(args[2]), args[1]);        
      else
        parser.parse(fileToURL(args[0]));        

      XMLDocument doc = parser.getDocument();
      DTD dtd = (DTD)doc.getDoctype();

      String doctype_name = null;

      if (args.length == 3)
      {
        doctype_name = args[1];
      }
      else
      {
        // get the Root Element name from the XMLDocument
        doctype_name = doc.getDocumentElement().getTagName();
      }

      // generate the Java files...
      ClassGenerator generator = new ClassGenerator();

      // set generate comments to true
      generator.setGenerateComments(true);
      // set output directory 
      generator.setOutputDirectory(".");
      // set validating mode to true
      generator.setValidationMode(true);

      // generate java src
      generator.generate(dtd, doctype_name);

    }
    catch (Exception e) 

    {
      System.out.println ("XML Class Generator: Error " + e.toString());
      e.printStackTrace();
    }
  }

  static public URL fileToURL(String sfile) 
  {
    File file = new File(sfile);
    String path = file.getAbsolutePath();
    String fSep = System.getProperty("file.separator");
    if (fSep != null && fSep.length() == 1)
      path = path.replace(fSep.charAt(0), '/');
    if (path.length() > 0 && path.charAt(0) != '/')
      path = '/' + path;
    try 
    {
      return new URL("file", null, path);
    }
    catch (java.net.MalformedURLException e) 
    {
      // Can only happen if the file
      // protocol were not recognized
      throw new Error("unexpected MalformedURLException");
    }
  }
}

Creating a Valid XML Document from Java Classes

import oracle.xml.classgen.*;
import oracle.xml.parser.*;

public class CreateEmployees
{
  public static void main (String args[]) 
    {
      try 
      {
        EMP EMPLIST = new EMP();
        DTD dtd = EMPLIST.getDTDNode(); // get static from base document

        // New employee emp_row1
        EMP_ROW emp_row1 = new EMP_ROW(1); // create row and set ROWNO 
        EMPNO empno1 = new EMPNO("7654"); 
        ENAME ename1 = new ENAME("MARTIN");
        JOB job1 = new JOB("SALESMAN");
        MGR mgr1 = new MGR("7698");
        HIREDATE hiredate1 = new HIREDATE("1981-09-28 00:00:00.0");
        SAL sal1= new SAL("1250");

        COMM comm1= new COMM("1400");
        DEPTNO deptno1 = new DEPTNO("30");
        
        // New employee emp_row2
        EMP_ROW emp_row2 = new EMP_ROW(2); // create row and set ROWNO 
        EMPNO empno2 = new EMPNO("7788"); 
        ENAME ename2 = new ENAME("SCOTT ");
        JOB job2 = new JOB("ANALYST ");
        MGR mgr1 = new MGR("7566");
        HIREDATE hiredate2 = new HIREDATE("1987-04-19 00:00:00.0");
        SAL sal2= new SAL("3000");
        COMM comm2= new COMM("");
        DEPTNO deptno2 = new DEPTNO("20");

        emp_row1.addnode(empno1); // Add data as tree nodes to emp_row1
        emp_row1.addnode(ename1); 
        ... 

        emp_row2.addnode(empno2); // Add data as tree nodes to emp_row2
        emp_row2.addnode(ename2); 
        ... 
       
        EMPLIST.addNode(emp1); // Add emp_row1 as tree node to 
                               // EMPLIST doc root 
        EMPLIST.addNode(emp2); // Add emp_row2 as tree node to
                               // EMPLIST doc root 

        EMPLIST.validateContent();
        EMPLIST.print(System.out);
      }

    catch (Exception e)
    {
        System.out.println(e.toString());
        e.printStackTrace();
    }
  }
}

XML Document Created by Java Application

<?xml version="1.0"?>
<!DOCTYPE EMP SYSTEM "employee.dtd">
<EMP>
  <EMP_ROW ROWNO = "1">
    <EMPNO>7654</EMPNO>
    <ENAME>MARTIN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>1981-09-28 00:00:00.0</HIREDATE>
    <SAL>1250</SAL>
    <COMM>1400</COMM>
    <DEPTNO>30</DEPTNO>
  /EMP_ROW>
  <EMP_ROW ROWNO = "2">
    <EMPNO>7788</EMPNO>
    <ENAME>SCOTT</ENAME>
    <JOB>ANALYST</JOB>
    <MGR>7566</MGR> 
    <HIREDATE>1987-04-19 00:00:00.0</HIREDATE> 

    <SAL>3000</SAL> 
    <COMM></COMM>
    <DEPTNO>20</DEPTNO>
  </EMP_ROW>
</EMP>

XML SQL Utility for Java

• Pass a query to the database and generate an XML document (text or DOM) from the results.
• Write XML data to a database table.

Generating XML from Query Results

The structure of the resulting XML document is based on the internal structure of the database schema that returns the query results. Columns are mapped to top level elements. Scalar values are mapped to elements with text-only content. Object types are mapped to elements with attributes appearing as sub-elements. Collections are mapped to lists of elements. Object references and referential constraints are mapped to XML IDREFs.

The utility can generate either a string representation of the XML document, or an in-memory XML DOM tree of elements. If you are returning the XML document to a requester, the string representation is best. You should use the DOM representation if you are going to operate on the XML programmatically, for example, transform it using the XSLT Processor or use DOM methods to search or modify the XML in some way.

You can also use the XML SQL Utility to generate a DTD based on the schema of the underlying table being queried. You can use the generated DTD as input to the XML Class Generator for Java, which will generate a set of classes based on the DTD elements. You can then write Java code that use these classes to generate the infrastructure behind a web-based form. Based on this infrastructure, the web form will capture user data and create an XML document compatible with the database schema. This data can then be written directly to the corresponding database table or object view without further processing. For more information about this approach, see Exchanging Data Among Applications.

Example: Generating XML from Query Results

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO = 7654;

<?xml version="1.0"?> 
<ROWSET> 
  <ROW id="1"> 
    <EMPNO>7654</EMPNO> 
    <ENAME>MARTIN</ENAME> 
  </ROW> 
</ROWSET>

Example: Generating XML from Query Results and Structuring the Data

The following Java code queries the database and constructs an XML file containing the results. The query is a simple select EMPNO, ENAME from EMP:

import java.sql.*;
import java.math.*;
import oracle.xml.sql.query.*;
import oracle.jdbc.*;
import oracle.jdbc.driver.*;

public class xmlquerydb
{
  public static void main(String args[]) throws SQLException
  {
    String tabName = "emp";
    String user = "scott/tiger";

    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    //initiate a JDBC connection
    Connection conn = 
      DriverManager.getConnection("jdbc:oracle:oci8:"+user+"@");

    // initialize the OracleXMLQuery

    OracleXMLQuery qry = new OracleXMLQuery(conn,"select EMPNO, 
        ENAME from "+tabName );

    // structure the generated XML document
    qry.setMaxRows(2);  // set the maximum number of rows to be returned
    qry.setRowsetTag("ROOTDOC"); // set the root document tag
    qry.setRowTag("DBROW"); // sets the row separator tag
    qry.setStyleSheet("emp.xsl"); // sets the stylesheet
    
    // get the XML document in string format
    String xmlString = qry.getXMLString();
    
    // print out the XML document
    System.out.println(" OUTPUT IS:\n"+xmlString); 
  }
}

<?xml version="1.0"?> 
<ROOTDOC> 
  <DBROW id="1"> 
    <EMPNO>7876</EMPNO> 
    <ENAME>ADAMS</ENAME> 
  </DBROW> 
  <DBROW id="2"> 
    <EMPNO>7499</EMPNO> 
    <ENAME>ALLEN</ENAME> 
  </DBROW> 
</ROOTDOC>

Writing XML to a Table

Storing an XML document in an Oracle8i table preserves the structure of the document. Element tag names are mapped to column names in the table. Elements with text-only content are mapped to scalar columns, and elements containing sub-elements are mapped to object types. Lists of elements are mapped to collections. Unstructured data like textual comments or descriptions cannot be mapped to underlying database tables, so this kind of data should be stored as a CLOB in the database. See Oracle8i and interMedia documentation for more information.

Example: Writing XML to a Table

import oracle.xml.sql.dml.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.*;
import java.net.*;

public class xmlwritedb
{
  public static void main(String args[]) throws SQLException
  {
    String tabName = "EMP"; // Table into which to insert XML data
    String fileName = "emp.xml"; // XML document filename
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Initialize a JDBC connection
    Connection conn = 
      DriverManager.getConnection("jdbc:oracle:oci8:scott/tiger@");

    // Insert XML data from file (filename) into 
    // database table (tabName)

    OracleXMLSave save = new OracleXMLSave(conn, tabName);
    URL url = save.createURL(fileName);
    int rowCount = save.insertXML(url);

    System.out.println(" successfully inserted "+rowCount+
                       " rows into "+ tabName);
    conn.close();
  }
}

XSQL Servlet

You can run this servlet in any web server that supports Java servlets. The following figure shows how data flows from a client, to the servlet, and back to the client. The sequence of events is as follows:

1. The user enters a URL through a browser, which is interpreted and passed to the XSQL Servlet through a Java Web Server. The URL contains the name of the target XSQL file (.xsql) and optionally, parameters, such as values and an XSL stylesheet name. Alternatively, the user can invoke the XSQL Servlet from the command line, bypassing the browser and Java web server.
2. The servlet passes the XSQL file to the XML Parser for Java, which parses the XML and creates an API for accessing the XML contents.
3. The page processor component of the servlet uses the API to pass XML parameters and SQL statements (found between <query></query> tags) to the XML SQL Utility. The page processor also passes any XSL processing statements to the XSLT Processor.
4. The XML SQL Utility sends the SQL queries to the underlying Oracle8i database, which returns the query results to the utility.
5. The XML SQL Utility returns the query results to the XSLT Processor as XML formatted text. The results are embedded in the XML file in the same location as the original <query> tags.
6. If desired, the query results and any other XML data are transformed by the XSLT Processor using a specified XSL stylesheet. The data can be transformed HTML or any other format defined by the stylesheet. The XSLT Processor can selectively apply different stylesheets based on the type of client that made the original URL request. This HTTP_USER_AGENT information is obtained from the client through an HTTP request.
7. The XSLT Processor passes the completed document back to the client browser for presentation to the user.

Example: XSQL Servlet

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="rowcol.xsl"?>
  <query connection="demo" 
    find="%"
    sort="ENAME"
    null-indicator="yes" >
    SELECT *  FROM EMP
        WHERE ENAME LIKE '%{@find}%'
            ORDER BY {@sort}
  </query>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/technology/">
    <html>
      <body class="page">
        <xsl:apply-templates/>      
      </body>
    </html>
  </xsl:template>

    <xsl:template match="ROWSET">
      <center>
      <table border="0" cellpadding="4">
      <xsl:choose>
        <xsl:when test="ROW">
          <!-- present headings: row[1] columns +-->
            <xsl:for-each select="ROW[1]">
            <tr>
              <xsl:for-each select="*">
                <th align="left">
                  <xsl:attribute name="class">
                  </xsl:attribute>
                  <xsl:value-of select="name(.)"/>
                </th>

              </xsl:for-each>
            </tr>
          </xsl:for-each>
          <xsl:apply-templates/>
        </xsl:when>
        <xsl:otherwise>
          <tr><td>No Matches</td></tr> 
        </xsl:otherwise>
      </xsl:choose>
      </table>
      </center>
    </xsl:template>

    <!-- present rows and columns +-->
    <xsl:template match="ROW">
        <tr>
          <xsl:attribute name="class">
          </xsl:attribute>
          <xsl:for-each select="*">
            <td>
              <xsl:attribute name="class">
              </xsl:attribute>
              <xsl:apply-templates select='.'/>
            </td>
          </xsl:for-each>
        </tr>
  </xsl:template>
</xsl:stylesheet>

http://localhost/xsql/demo/emp.xsql?find=T&sort=EMPNO

XML Transviewer Java Beans

• DOM Builder Bean
• XML Source Viewer Bean
• XML Tree Viewer Bean
• XSL Transformer Bean

DOM Builder Bean

XML Source Viewer Bean

XML Tree Viewer Bean

XSL Transformer Bean

XML Schema Processor

• Part 0: Primer XML Schema
• Part 1: Structures XML Schema
• Part 2: Datatypes

Oracle9i Product Family

• Built-in, native functions linked to the Oracle9i kernel for generating XML. Three new, highly-scalable PL/SQL functions (SYS_XMLGEN, SYS_XMLAGG, and DBMS_XMLGEN) provide an alternative to the XDK for working within the database to generate XML.
• Native XML type support. For applications that need to store and retrieve large amounts of complex XML, Oracle9i stores XML natively by introducing XMLType, a new object datatype, and features extremely fast, navigational access and searches for XML documents.
• The UriType family of types can store and query URI-refs in the database. SYS.UriType is an abstract object type that provides functions to access the data pointed to by the URL. SYS.HttpUriType and SYS.DBUriType are subtypes of UriType. The HttpUriType can store HTTP URLs and the DBUriType can store intra-database references.
• New Oracle Text XML-searching capabilities including new operators HASPATH and INPATH that leverage XPath expressions within Oracle Text queries. You can use Oracle Text to perform searches on XML documents stored in Oracle9i by indexing the XML as plain text, or as document sections for more precise searches, such as find "Oracle WITHIN title" where "title" is a section of the document.
• Oracle Advanced Queuing (AQ) uses XML to provide infrastructure for e-business integration. Specifically, AQ defines a presentation named iDAP (Internet Data Access Presentation) using XML. With iDAP, AQ operations can be executed  via Internet transport protocols such as HTTP(S), SMTP, and FTP. AQ supports XML datatype payloads and allows definitions of subscriptions based on contents of XML messages.

XML in the Database

• Storing an XML document as a single, intact object with its tags in a CLOB or BLOB.
• Storing an XML document as data and distributing it untagged across object-relational tables.
• Combining XML documents and data using views.

Storing XML Documents as CLOBs or BLOBs

Storing XML Documents as Data

• Store the attributes of the elements in a relational table and define object views to capture the structure of the XML elements.
• Store the structured XML elements in an object table.

The XML SQL Utility provides the means to then store an XML document by mapping it to the underlying object-relational storage, and conversely, provides the ability retrieve the object-relational data as an XML document.

If an XML document is structured, but the structure of the XML document is not compatible with the structure of the underlying database schema, you must transform the data into the correct format before writing it to the database. You can achieve this using XSL stylesheets or other programming approaches, but depending on your needs, you might want to store the data-centric XML document as an intact single object. Alternately, you can define object views corresponding to the various XML document structure and define instead-of triggers to perform the appropriate transformation and update the base data.

Combining XML Documents and Data Using Views

Oracle Internet File System

Many development tasks can be done using XML alone. Since the entire API is exposed and documented, advanced file system and content management features are readily available to developers. Here are some examples of development projects that the Oracle Internet File System makes possible:

• Extend XML support to create new XML Applications.
• Build a documentation suite that uses XML.
• Create custom parsers and renderers to manipulate documents according to your business problem set.
• Build a custom parser that renders Word files containing graphics as PDFs, and renders Word files containing XML spreadsheets as HTML files.
• Invoke new presentation methods for specific file types.
• Build a custom parser that associates files with one of several custom rendererers based on custom attributes within the files.
• Design and implement security schemes using XML.
Create an XML template to streamline the process of adding new user accounts in several applications and folders.

Oracle9i AS Product Family

Oracle9iAS Forms Services

Oracle9iAS Portal

Oracle9iAS Wireless Edition

Oracle Reports

Oracle Internet Developer Suite

For example, Oracle Internet Developer Suite includes Oracle JDeveloper, which uses XML internally and enables development of XML applications. JDeveloper offers many features that enable developers to create business-to-consumer and business-to-business XML applications. JDeveloper can be utilized to write XML documents and XSL Stylesheets, to generate XML on the fly using the Business Components for Java Framework or the database directly and to transform XML into HTML, WML, XML or any other format. JDeveloper integrates the various XML Utilities that Oracle has produced, including the Oracle XML Parser for Java, the XML SQL Utility and the XSQL Servlet. JDeveloper provides an integrated development environment for developers by offering editors, compilers and (remote) debuggers for Java and XML.

Internally, the Business Components for Java Framework uses XML to store metadata about its application components. Important information is stored in a structured document rather than in Java source code. This makes applications easier to customize, because you don't need access to the Java source code.

Oracle E-Business Suite

As a key component of Oracle's application integration framework, XML Messaging Services comprise an event-driven, standards-based solution for exchanging and integrating XML messages within the Oracle E-Business Suite. For example, in the Oracle Order Management application, the interfaces for invoices, ship notices, purchase orders, and other business object documents (BODs) are all converted to XML. Similarly, in manufacturing and supply chain applications, XML transactions communicate specifications to a subcontractor or component manufacturer.

OTN as Developer Service Provider

OracleMobile Online Studio

XML is a key technology, enabling Online Studio to support multiple wireless device formats. Software modules called transformers use XSL and device-specific processing to optimize layout for each device type. Transformers are updated and maintained rigorously to maintain compatibility with the device market.
 

Oracle Portal Studio

XML plays an important role in the Portal Studio and the PDK. For example, applications called providers parse XML documents for data, including properties such as location and implementation details, about the portlets they provide.

< Back to Introduction

Questions or comments? Post a message in OTN's XML discussion forum.

Using XML in Oracle Database Applications: Part 2: About Oracle XML Products
Author: Brad Wait, Oracle Corporation
Date: November 1999. Updated June 2001 by Robert Hall, Oracle Corporation.

This document is provided for information purposes only and the information herein is subject to change without notice. Please report any errors herein to Oracle Corporation. Oracle Corporation does not provide any warranties covering and specifically disclaims any liability in connection with this document.
 

Oracle is a registered trademark and Enabling the Information Age is a trademark or registered trademark of Oracle Corporation. All other company and product names mentioned are used for identification purposes only and may be trademarks of their respective owners.

Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA94065
U.S.A.

Worldwide Inquiries:
+1.650.506.7200
Copyright © Oracle Corporation 1999, 2000, 2001
All Rights Reserved