As Published In
Oracle Magazine
July/August 2005

DEVELOPER: PHP


Using PHP 5 with Oracle XML DB

By Yuli Vasiliev

Combine the power of object-oriented PHP and Oracle XML DB.

As a programming language, PHP has many strengths, including ease of use, short development time, and high performance. With a brand-new object model, PHP 5 leverages the power and flexibility of object-oriented programming.

This fifth release of PHP brings many more fully object-oriented capabilities to the language, including interfaces; abstract classes; private, public, and protected access modifiers; and static members and methods. PHP 5 also now passes objects by reference rather than by value.

PHP provides several built-in class libraries you can easily extend, thus putting the promise of reuse into immediate practice. This article covers extending one of the classes from PHP's Document Object Model (DOM) library, creating a subclass that interacts with Oracle XML DB. It examines some of the object-oriented features of the PHP classes and goes through some changes to the sample subclass that exercise various ways of working with Oracle XML DB from PHP.

Demonstrating Inheritance Features of PHP 5 Using Oracle XML DB

In PHP 5, a class inherits the functionality of an existing base class by using the extends keyword. Just as in Java and other object-oriented programming languages, you can extend both user-defined and predefined PHP classes in PHP 5. The DOM function library is one of the predefined PHP classes available automatically in PHP 5—it provides functions for creating, populating, and otherwise working with XML DOM structures (document nodes, elements, and so on); it is an object-oriented class library that follows DOM Level 2 as much as possible (the PHP 5 DOM library replaces PHP 4's DOM XML extension).

Extending a Built-in Class

In Listing 1, the MyDomDocument class code in MyDomDocument.php illustrates how you can extend the predefined DomDocument class with a public method that is intended to simplify the process of creating XML documents.

Code Listing 1: MyDomDocument class extends DomDocument 

<?php
  //File: MyDomDocument.php
  class MyDomDocument extends DomDocument{
    
    public function __construct($ver = "1.0", $encode = "UTF-8")
    {
     parent::__construct($ver, $encode);
    }
    public function addElement($node, $elemName, $elemVal="")
    {
     if(!$child = $this->createElement($elemName, $elemVal))
       throw new Exception('Could not create a new node ');
     if(!$child = $node->appendChild($child))
       throw new Exception('Could not append a new node ');
     return $child;
    }
  }
?>


In the MyDomDocument.php code, you explicitly call the parent constructor within the overriding constructor. Unlike other object-oriented languages, PHP doesn't implicitly invoke the parent constructor when the subclass calls its constructor, which means that if you define a constructor in a child class, you must explicitly call the constructor of the parent class from within the new child constructor. Also note that, as with Java, only members and methods defined in the parent as public or protected are accessible within a child class.

Regardless of whether a child class overrides a public or protected parent method or member, you can still access it within that child class, by using the parent:: prefix. To client code, any method or member defined as public in the parent class will be accessible with an instance of the child class, by default, unless this child class has overridden that parent method or member. (Methods and members defined as final in the parent class cannot be overridden in child classes.)

Now let's put the MyDomDocument class to use. The dom.php script shown in Listing 2 creates an instance of MyDomDocument and populates an XML DOM structure. If you load dom.php in the /htdocs (or other appropriate directory) of your Web server and open the file in a Web browser, you'll see the following output when you view the source: 

<?xml version="1.0" encoding="UTF-8" ?> 
  <EMPLOYEE id="1">
    <EMPNO>212</EMPNO> 
    <ENAME>John Jamison</ENAME>
    <TITLE>Programmer</TITLE>
  </EMPLOYEE>


Code Listing 2: dom.php creates an XML document 

<?php
  //File: dom.php
  require_once 'MyDomDocument.php';
  try {
    //first, create an instance of MyDomDocument
    $dom = new MyDomDocument();
    //now, create the root of the XML document
    $root = $dom->addElement($dom, 'EMPLOYEE');
    //you can always add an attribute to the node if necessary
    $root->setAttribute('id', '1');
    //once the root element is created, add nested nodes as needed
    $emplno = $dom->addElement($root, 'EMPNO', '212');
    $ename = $dom->addElement($root, 'ENAME', 'John Jamison');
    $title = $dom->addElement($root, 'TITLE', 'Programmer');
    //finally, output the XML document
    echo $dom->saveXML();
  }
  catch(Exception $e) {
       print $e->getMessage();
  }
?>


Unlike this simple example and its hard-coded data in the test script, a real-world script most likely would retrieve data from a file or a database, as shown in domXML.php in Listing 3.

Code Listing 3: domXML.php gets element information from the database 

<?php
  //File: domXML.php
  require_once 'MyDomDocument.php';
  require_once 'dbConn5.php'; 
  require_once 'ScottCred.php';
  $i = 0;
  try {
    $dom = new MyDomDocument();
    $root = $dom->addElement($dom, 'EMPLOYEES');
    $db = new dbConn5($user, $pswd, $conn);
    $sql="SELECT EMPNO, ENAME, JOB FROM EMP";
    $db->query($sql);
    while ($row = $db->fetch()) {
      $empl = $dom->addElement($root, 'EMPLOYEE');
      $empl->setAttribute('id', ++$i);
      $empno = $dom->addElement($empl, 'EMPNO', oci_result($row,'EMPNO'));
      $ename = $dom->addElement($empl, 'ENAME', oci_result($row,'ENAME'));
      $title = $dom->addElement($empl, 'TITLE', oci_result($row,'JOB'));
     }
    print $dom->saveXML();
  }
  catch(Exception $e) {
       print $e->getMessage();
  }
?>


Note that domXML.php in Listing 3 includes two other scripts—the ScottCred.php (Listing 4) script and the dbConn5.php (Listing 5) script. ScottCred.php contains the logon credentials and connection string information for the Oracle database, and dbConn5.php comprises a PHP 5-compliant dbConn5 connection string class. You'll want to modify the particulars ( $user, $pswd , and connection string information) of the ScottCred.php file for your specific environment if you run the sample code for this article, but you can leave dbConn5 .php as is. (For information on setting up your environment to run the sample code in this article, see the section " Basic Setup for the Samples .")

Code Listing 4: ScottCred.php contains logon and connection information 

<?php
  //File: ScottCred.php
  $user="scott";
  $pswd="tiger";
  $conn="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
          )
         (CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))
       )";
?>


Code Listing 5: dbConn5 connection class 

                               
<?php
  //File: dbConn5.php
  class dbConn5 {
    private $user;
    private $pswd;
    private $db;
    private $conn;
    private $query;
    const CONNECTION_ERROR = 1;
    const SQLEXECUTION_ERROR = 2;
    function __construct($user, $pswd, $db)
    {
      $this->user = $user;
      $this->pswd = $pswd;
      $this->db = $db;
      $this->ConnToDb();
    }
    function ConnToDb()
    {
    if(!$this->conn = oci_connect($this->user, $this->pswd, $this->db))
       {
        $err = oci_error();
throw new Exception('Could not establish ... : '
. $err['message'], self::CONNECTION_ERROR);
} } function query($sql) { if(!$this->query = oci_parse($this->conn, $sql)) { $err = oci_error($this->conn); throw new Exception('Failed to execute SQL ... : '
. $err['message'], self::SQLEXECUTION_ERROR);
} else if(!oci_execute($this->query)) { $err = oci_error($this->query); throw new Exception('Failed to execute SQL ... : '
. $err['message'], self::SQLEXECUTION_ERROR);
} return true; } function fetch() { if(oci_fetch($this->query)){ return $this->query; } else { return false; } } } public function fetchAll() { if(oci_fetch_all($this->query, $results)){ return $results; } else { return false; } } ?>


The domXML.php script in Listing 3 also extends the MyDomDocument class, but first it selects relational data from the database.

Alternatively, you can use SQL XML functions to achieve the same general result, in which case you would want to use the domTaggedXML.php script in Listing 6 to generate the XML data.

Code Listing 6: domTaggedXML.php—SQL XML alternative to domXML.php 

                               
<?php
  //File: domTaggedXML.php
  require_once 'MyDomDocument.php';
  require_once 'dbConn5.php'; 
  require_once 'ScottCred.php';
  try {
   $dom = new MyDomDocument();
   $db = new dbConn5($user, $pswd, $conn);
   $sql="SELECT (XMLELEMENT(\"EMPLOYEE\", XMLATTRIBUTES(rownum AS \"id\"), 
XMLELEMENT(\"EMPNO\", empno), XMLELEMENT(\"ENAME\", ename)
                                

                                 
[[[remove this line break to use code]]], 
XMLELEMENT(\"TITLE\", job))).getClobVal() as MYXML FROM EMP";
   $db->query($sql);
   $rows=$db->fetchAll();
   foreach ($rows['MYXML'] as $row) {
     $rslt=$rslt.$row;
   }
   $dom->loadXML("<EMPLOYEES>$rslt</EMPLOYEES>");
   print $dom->saveXML();
  }
  catch(Exception $e) {
       print $e->getMessage();
  }
?>
                              
                            

Nonetheless, in either case—with domXML.php or domTaggedXML.php—the result is the same. Assuming you're connected to an Oracle database that has the default SCOTT database schema, with its EMP table, domXML.php or domTaggedXML.php would generate this XML output: 

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEES>
  <EMPLOYEE id="1">
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <TITLE>CLERK</TITLE>
  </EMPLOYEE> 
 ...
</EMPLOYEES>


This XML output is transient—it exists only in the context of the Web browser, for the moment. Let's go a step further and save the XML content (stored in the internal XML tree of the MyDomDocument class object) to an Oracle database, using Oracle XML DB.

Using the Database to Save Your XML

Proceeding with the example, you can go one step further and save the XML content stored in the internal XML tree of the MyDomDocument instance to an Oracle database. You might, for example, want to extract the employee XML elements from the XML tree and save them as individual separate employee XML documents in the database.

To do this, you must first create a database schema in which to store the employee XML documents, defining an XMLType for storage of employee XML documents using an XML Schema definition. (See the section "Basic Setup for the Samples" for information on schema setup.) Once you've set up the XML storage, you can add a public method to the MyDomDocument class that inserts XML Schema-based data into the Oracle database for you, as shown in Listing 7.

Code Listing 7: insertSchemaBasedXML() function 

                               
public function insertSchemaBasedXML($tagName, $db, $table, $schema)
  {
   try {
    $items = parent::getElementsByTagName($tagName);
    for ($i = 0; $i < $items->length; $i++) {
     $nodeXML = parent::saveXML($items->item($i)) . "\n"; 
     $db->query("INSERT INTO " . $table . " VALUES( 
XMLType('" . $nodeXML . "').createSchemaBasedXML('" . "$schema" . "'))");
       }
     }
     catch (Exception $e) {
           throw $e;
    }
     print "Data have been submitted";
  }

                            

As the code in Listing 7 shows, you use the parent keyword to call the parent class's methods. (Alternatively, you can explicitly specify the parent class by name—for example, DomDocument —rather than using the parent keyword.)

Listing 7 demonstrates multiple PHP objects interacting—you can dynamically associate two objects by passing a reference to one object as a parameter to a method of another object. In this example, the insertSchemaBasedXML() method takes an instance of dbConn5 as a parameter and then invokes the query() method to perform the INSERT operation against the database.

With the insertSchemaBasedXML() method in place, you don't need to compose individual INSERT statements to insert schema-based XML data into an Oracle database: insertSchemaBasedXML() will do it for you.

As shown by the insertSchemaBasedXML() method signature in Listing 7, you must specify the following parameters: 

  • A tag name ( $tagname ) to search for appropriate elements within the internal XML tree of the MyDomDocument instance. Each of these elements will be used as an independent XML document.

  • An instance of the dbConn5 class to interact with the database.

  • The XMLType table ( $table ) into which to insert the XML content.

  • A registered XML schema to validate the inserted XML documents.

As a result, the work of inserting the generated employee XML documents into the database is reduced to the following: 

$dom->insertSchemaBasedXML(
'EMPLOYEE', $db2,'employee', 
'http://localhost:8080/public/emp.xsd');


The domXML_db.php script in Listing 8 exercises this function call.

Code Listing 8: domXML_db.php client 

                               
<?php
  //File: domXML_db.php
  require_once 'MyDomDocument.php';
  require_once 'dbConn5.php'; 
  require_once 'ScottCred.php';
  try {
    $dom = new MyDomDocument();
    $root = $dom->addElement($dom, 'EMPLOYEES');
    $db = new dbConn5($user, $pswd, $conn);
    $sql="SELECT EMPNO, ENAME, JOB FROM EMP";
    $db->query($sql);
    while ($row = $db->fetch()) {
      $empl = $dom->addElement($root, 'EMPLOYEE');
      $emplno = $dom->addElement($empl, 'EMPNO', oci_result($row,'EMPNO'));
      $ename = $dom->addElement($empl, 'ENAME', oci_result($row,'ENAME'));
      $title = $dom->addElement($empl, 'TITLE', oci_result($row,'JOB'));
     }
     $db2 = new dbConn5('usr', 'pswd', $conn);
     $dom->insertSchemaBasedXML('EMPLOYEE', $db2,'employee', 
'http://localhost:8080/public/emp.xsd');
  }
  catch(Exception $e) {
       print $e->getMessage();
  }
?>

                            

After running domXML_db.php, you can confirm the results by using SQL*Plus from the command line (after logging on as PHPUSR ), as follows: 

SELECT COUNT(*) FROM employee;

 COUNT(*)
 ----------------
         14


The result demonstrates that 14 rows have been successfully moved from the emp table under the SCOTT demonstration schema into the employee XMLType table in the PHPUSR database schema. If you try to run the domXML_db.php script more than once, you'll get the following error message:

 

Failed to execute SQL query: ORA-00001: unique constraint  (PHPUSR.EMPNO_IS_PRIMARYKEY) violated


The insertSchemaBasedXML() method employs a try-catch statement to detect and respond to the errors that can occur when you are inserting XML content into an XMLType table in the database. Although this may seem odd—a class definition typically doesn't include try-catch routines—it makes sense in this example, because the interaction is between two classes: The code in the try block calls the query() method in the dbConn5 class, which can throw an exception. The query() method throws an exception if it can't perform the query against the database, so if you omit the try-catch statement in the insertSchemaBasedXML() method, an exception thrown in the query() method will be caught only in the calling script.

This means that the insertSchemaBasedXML() method will not terminate when query() fails to insert a row (an XML document) into the specified XMLType table. Instead, it will try to insert the following row until it reaches the end of the list of the generated XML documents.

To prevent this behavior, you simply wrap the code calling the query() method in the try block and define a catch block that, in response to an exception, will throw a new exception, thereby terminating insertSchemaBasedXML() execution when the first failure to insert a row occurs.

However, if you want the insertSchemaBasedXML() method to continue execution when an insert failure has occurred, thereby trying to insert the following row, you can simply replace the line of code that throws an exception in the catch block with a line that just prints a warning message. In this case, the catch block in the insertSchemaBasedXML() method would look like this: 

catch (Exception $e) {
   print 'Could not insert 
   a row into the ' . $table . 
   ' table: ' . $e->getMessage();
   }


Other Avenues for Moving XML Data

The previous section demonstrated how you can move XML data in an Oracle database with PHP by means of standard SQL. In fact, Oracle XML DB provides you with different strategies for moving XML content into and out of the Oracle database.

For example, you can use industry-standard internet protocols, such as FTP, HTTP, or WebDAV, to insert XML content into the database. In this case, you simply put the files that contain the schema-based XML content into a folder in the Oracle XML DB repository. Oracle XML DB then implicitly inserts the appropriate rows into the table specified as the default table for that XML schema.

For example, to add a function to insert XML via FTP programmatically, you could add an insertXMLByFTP() public method to the MyDomDocument class (of Listing 1), as shown in Listing 9.

Code Listing 9: insertXMLByFTP—internet protocol alternative to SQL 

                               
public function insertXMLByFTP($tagName, $table, $user,
$pswd, $ftp_dir, $host, $port=2100, $timeout=30)
{ try { $items = parent::getElementsByTagName($tagName); $con_id = ftp_connect($host, $port, $timeout); $login = ftp_login($con_id, $user, $pswd); ftp_chdir($con_id, $ftp_dir); for ($i = 0; $i < $items->length; $i++) { $nodeXML = parent::saveXML($items->item($i)) . "\n"; $dest_file = $tagName . $i . '.xml'; $temp = tmpfile(); fwrite($temp, $nodeXML); fseek($temp, 0); if(!ftp_fput($con_id, $dest_file, $temp, FTP_ASCII)) throw new Exception("Cannot put $dest_file"); fclose($temp); } } catch (Exception $e) { throw $e; } print "Data have been submitted"; }


The domXMLFTP_db.php script in Listing 10 shows the insertXMLByFTP() method in action. (Before attempting to run domXMLFTP_db.php, you must delete any rows that were inserted into the employee table by previous script runs, to avoid integrity constraint violations.)

Code Listing 10: domXMLFTP_db.php exercises insertXMLByFTP function 

                               
<?php
  //File: domXMLFTP_db.php
  require_once 'MyDomDocument.php';
  require_once 'dbConn5.php'; 
  require_once 'ScottCred.php';
  try {
    $dom = new MyDomDocument();
    $root = $dom->addElement($dom, 'EMPLOYEES');
    $db = new dbConn5($user, $pswd, $conn);
    $sql="SELECT EMPNO, ENAME, JOB FROM EMP";
    $db->query($sql);
    while ($row = $db->fetch()) {
      $empl = $dom->addElement($root, 'EMPLOYEE');
$empl->setAttribute('xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance');
$empl->setAttribute('xsi:noNamespaceSchemaLocation', 'http://localhost:8080/public/emp.xsd');
      $emplno = $dom->addElement($empl, 'EMPNO', oci_result($row,'EMPNO'));
      $ename = $dom->addElement($empl, 'ENAME', oci_result($row,'ENAME'));
      $title = $dom->addElement($empl, 'TITLE', oci_result($row,'JOB'));
     }
    $dom->insertXMLByFTP('EMPLOYEE', 'employee', 'usr', 'pswd', '/public', 'localhost');
  }
  catch(Exception $e) {
       print $e->getMessage();
  }
?>

                            

The domXMLFTP_db.php script in Listing 10 creates the appropriate schema-based employee XML document for each row stored in the emp table under the SCOTT/TIGER demonstration schema and then puts that XML document as a file into the /public directory of the Oracle XML DB repository. Oracle Database, in turn, automatically inserts the appropriate row into the employee table of XMLType in the PHPUSR database schema. You can confirm the success of the INSERT operations by logging on to the database as PHPUSR from SQL*Plus and obtaining the row count as follows: 

SELECT COUNT(*) FROM employee;

  COUNT(*)
------------------
         14

 

Next Steps


 READ more about
 Oracle XML DB
Oracle and PHP

 DOWNLOAD Oracle Database 10g Oracle XML DB

Although executing domXMLFTP_db.php and domXML_db.php generates the same result, what is not immediately apparent from these examples is actually a fairly significant difference in how the SQL-based approach and the internet-protocol-based approaches work. Specifically, whereas FTP and other internet protocols don't let you explicitly control transactions from your PHP code, the SQL-based approach does.

Supporting Transactions

By default, the oci_execute() function (used throughout our SQL-based examples) is not transactional—the default execution mode value is OCI_COMMIT_ON_SUCCESS . However, you can easily change the default behavior to explicitly control the transaction from your PHP script, by specifying the OCI_DEFAULT execution mode as the second parameter in the oci_execute() call, as shown in Listing 11.

Code Listing 11: Controlling the transaction from PHP 

public function query($sql, $mode = OCI_COMMIT_ON_SUCCESS)
    {
      if(!$this->query = oci_parse($this->conn, $sql)) {
          $err = oci_error($this->conn);
           throw new Exception('Failed to execute SQL query: ' . $err['message']);
      }
      else if(!oci_execute($this->query, $mode)) {
          $err = oci_error($this->query);
           throw new Exception('Failed to execute SQL query: ' . $err['message']);
         }
       return true;
    }


Next, add the following public methods to the dbConn5 class: 

    public function commit()
    {
      if(!oci_commit($this->conn)) {
         return false;
        }
      return true;
    }
    public function rollback()
    {
      if(!oci_rollback($this->conn)) {
         return false;
        }
      return true;
    }


With the preceding dbConn5 class methods in place, you may want to add a new insertSchemaBasedXML_trans() public method to the MyDomDocument class, as shown in Listing 12.

Code Listing 12: insertSchemaBasedXML_trans function inserts or rolls back 

                               
public function insertSchemaBasedXML_trans($tagName, $db, $table, $schema)
  {
   try {
    $items = parent::getElementsByTagName($tagName);
    for ($i = 0; $i < $items->length; $i++) {
     $nodeXML = parent::saveXML($items->item($i)) . "\n"; 
       $db->query("INSERT INTO " . $table . " VALUES( 
XMLType('" . $nodeXML . "').createSchemaBasedXML('" . "$schema" . "'))", OCI_DEFAULT);
       }
     if(!$db->commit())
       print "Failed to commit the transaction";
     }
    catch (Exception $e) {
      if($db->rollback())
        print "Failed to rollback the transaction";;
      throw $e;
    }
     print "Data have been submitted";
  }

                            


Now you may want to replace the insertSchemaBasedXML() method with insertSchemaBasedXML_trans() in the domXML_db.php script, to guarantee that all the generated XML content will be inserted into the employee table. If the method fails to insert at least one row, the entire transaction is rolled back.

Conclusion

Taking advantage of object orientation in PHP 5 lets you write reusable code to move XML content into and out of Oracle XML DB. With a wide range of predefined classes—which you can reuse or extend as needed, along with access via SQL, FTP, HTTP, and WebDAV—available in PHP 5, you can design even-more-complex PHP/Oracle XML DB applications with a minimum of effort.

 

Basic Setup for the Samples


There are many "moving parts" when it comes to running the code listings accompanying this article. First, you must have Oracle Database (preferably, Oracle Database 10g, but Oracle9i R2 will work too). You must also have a Web server that supports PHP 5, such as Oracle Application Server 10g Release 2. For the code in this article, Apache 2 HTTP server, PHP 5, and Oracle Database 10g were installed and running locally, on a single server, but you can also set up the Web server with PHP and the database on different machines on your network.

In either case, be sure to run the PHP scripts from Apache's /htdocs directory (or another appropriate location, per your Apache configuration) and follow the instructions in the following section, "Oracle XML DB Setup for the Samples," to prepare the database for the examples.

Oracle XML DB Setup for the Samples

The sample code lets you extract employee XML elements from the XML tree (which you create with PHP) and save them as separate employee XML documents in the database. Before you can store the XML documents, however, you must first create a database schema in which to store them.

The simplest way of doing this is to design the appropriate annotated XML schema for the employee XML documents and then register the XML schema (an .xsd file) with the Oracle database. When you register the XML schema, Oracle Database automatically creates the required database schema for you.

To start, you must log on to the database instance (using an account with SYSDBA privileges) and create a database user—say, PHPUSR —as in 

SQL> create user phpusr identified by phpusr;

User created.


The next step is to grant the RESOURCE and CONNECT roles to the newly created user: 

SQL> grant connect, resource to phpusr;

Grant succeeded.


Next, you must connect as PHPUSR 

SQL> connect phpusr/phpusr
Connected.


and execute the following PL/SQL code to register the annotated XML schema (emp.xsd) that will validate employee XML documents: 

BEGIN
DBMS_XMLSCHEMA.registerSchema(
'http://localhost:8080/public/emp.xsd',
xmltype('<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb"
           version="1.0" >
  <xs:element name="EMPLOYEE" type="EmpType" 
              xdb:defaultTable="EMPLOYEE"/>
  <xs:complexType name="EmpType" xdb:SQLType="EMP_T">
    <xs:sequence>
      <xs:element name="EMPNO" type="EmpNoType" minOccurs="1" 
                  xdb:SQLName="EMPNO"/>
      <xs:element name="ENAME" type="EnameType" xdb:SQLName="ENAME"/>
      <xs:element name="TITLE" type="TitleType" 
                  xdb:SQLName="TITLE"/>
    </xs:sequence>
  </xs:complexType>
  <xs:simpleType name="EmpNoType">
    <xs:restriction base="xs:integer"/>
  </xs:simpleType>
  <xs:simpleType name="EnameType">
    <xs:restriction base="xs:string">
      <xs:minLength value="2"/>
      <xs:maxLength value="30"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="TitleType">
    <xs:restriction base="xs:string">
      <xs:minLength value="2"/>
      <xs:maxLength value="30"/>
    </xs:restriction>
  </xs:simpleType>
</xs:schema>'),
TRUE,TRUE,FALSE,TRUE
);
END;
/
PL/SQL procedure successfully completed.


Finally, you should define the PRIMARY KEY constraint on the employee XMLType table, to prevent duplicate employee records, as follows:

 

ALTER TABLE EMPLOYEE
ADD constraint EMPNO_IS_PRIMARYKEY
PRIMARY KEY (xmldata."EMPNO");
Table altered.


When you execute the PL/SQL code to register the XML schema, Oracle Database automatically creates the employee table of XMLType, which will be used to store employee XML documents that conform to the emp.xsd (the employee XML schema).




Yuli Vasiliev (jvyul@yahoo.com) is a software developer, freelance author, and consultant who focuses mainly on Oracle object and Oracle XML technology.

 



Send us your comments