XML and Oracle9i Release 2
XML and Oracle9i Release 2
By: William Cowan
Aug. 27, 2003 12:00 AM
With Oracle9i RDBMS Release 2, Oracle has moved further along on the road to a more complete implementation of XML in its database. This release also marks, I believe, one of Oracle's most significant uses of the object-relational capabilities of the database. By combining these capabilities with XML and adhering to W3C standards, Oracle has in the current release a strong contender in the XML-enabled database marketS and without losing the relational capabilities of the database.
Oracle9i Release 2 marks several other unique features for the Oracle database. One of these features is that you can create objects in the database without using DDL (Data Definition Language) and, consequently, without the intervention of database architects and/or administrators. With the current release an XML Schema can be used to create database objects needed to hold XML documents. The part of the Oracle database that holds the schema definition and XML documents is called the XDB. When an XML Schema is saved in the Oracle XDB and then registered with the database, the structures necessary to support XML documents that conform to that XML Schema are created in the database as relational objects. As XML documents are loaded into the database, the contents are automatically distributed among the objects in the database. They can then be retrieved with standard XPath expressions, or through PL/SQL and Java.
Another unique feature is that the loading of XML documents into the XDB has been greatly simplified. In the past, XML documents would have been loaded through Java or PL/SQL code, calling various APIs to parse, validate, and load the documents into the database. With the current release, Oracle has added capabilities to the standard out-of-the-box database listener. In the past this listener would listen on one or more ports for any connection requests, data requests, etc., from the SQL*Net interface; relay those requests to the database for action; and then return the results. The new listener also listens on additional ports for FTP requests and HTTP requests. When it receives these FTP or HTTP requests, the listener loads or retrieves the data from the Oracle XDB. If the document being FTP'd is an XML document associated with an XML Schema that has already been registered with the XDB, then the contents of the document are automatically "shredded" into the object-relational structures created by registering the XML Schema.
Finally, through FTP clients or HTTP-enabled applications, such as Windows File Explorer, the Oracle XDB looks like a file system. Structures within the XDB are represented as folders or directories with files. These files, even if they are XML documents that have been shredded to various objects in the database, can be retrieved and viewed with any HTTP-enabled application, such as Microsoft Word, XMLSPY, or a browser. When XML documents are stored in the XDB, Oracle is aware of the sequence within the XML document. When retrieved, the document is restored to its original structure.
Access to the Oracle XDB
To create new directories or folders in the XDB, you can just add them in the File Explorer as you would add any directory, or you can use Java or PL/SQL to add directories programmatically with a call to the system-provided stored procedure dbms_xdb.createfolder().
Using XMLSPY with Schemas
Author: William Cowan
I converted this to a simple XML document by making the name into a tag, and the value was the value for that tag. I then added the directory that contained these documents to my project in XMLSPY and used these documents as samples to create a new schema. Samples of the documents are available at www.sys-con.com/xml/sourcec.cfm.
Preparing the Schema for the Oracle XDB
as an attribute of the xs:schema tag. In addition, if you used XMLSPY to generate the schema, there will be a root element, in this case called "Firstsearch," which is a complex type that consists of all the complex types and elements that make up the XML documents. If this root element does not exist, you will need to add it. You then need to add the attribute to this element of:
xdb:SQLName is the name that will be given to the base object-relational table in the Oracle XDB for all the XML documents that conform to this schema.
In addition, each complex type in the schema will need an additional attribute of:
where the value for xdb:SQLType defines the name of an object-relational object in the database that will contain the elements and values from the XML document for that part of the document represented by the complex type. The xdb:SQLType value must be unique within the Oracle user in which this schema is being registered. If you do not define the xdb:SQLType attribute for a complex type in the schema, when you register that schema with the Oracle XDB Oracle will create its own internal identifier, which won't be as easy to remember as the one you define yourself.
Once you've added these attributes to the elements in the schema, you can register your schema with the Oracle XDB.
Registering the Schema with the Oracle XDB
Remember, the schema must be stored in the XDB first before you can register it. The package module registerSchema expects a folder path in the XDB.
XMLSPY allows you to register the schema directly from within XMLSPY. Choose "Convert/Oracle XML DB/Add SchemaS" from the menu and XMLSPY will first ask you to save the schema to the XDB, and then ask if you want to register it.
Loading XML Documents into the Database
Once you have added this line to all the documents you want to load, you can load documents into the Oracle database through any FTP client by connecting to the TNS Listener port 2100 and the server with the Oracle XDB. When the FIRSTSEARCH schema was registered with the database, it created a base table named "firstsearch," the value from the xdb:SQLName attribute. Then, for each element in the FIRSTSEARCH schema, Oracle creates a column in this table. For each complex type in the schema, Oracle creates an object-relational table using the value from the xdb:SQLType attribute to name the object, associated with the base table. As an XML document associated with the registered schema is loaded, the content is "shredded" among these objects in the database.
Now that you have loaded XML documents into the Oracle XDB you can retrieve information from those documents using XPath expressions, SQL, or Java. In addition, you can create views in Oracle that reference XDB objects so you are able to use any SQL code in Oracle that functions with views. And since these XDB objects exist in the Oracle database, you can associate them with existing relational tables to put further constraints on the loading of XML documents.
For instance, you might have an XML document based on a customer invoice, and you might want to make sure you load the invoice only if the customer exists in your customer table. You can add a constraint to the database that will ensure that the customer name or number in the XML document exists in the customer table before it will allow the document to be loaded into the XDB. In fact, the Oracle database will report this as an error to your FTP client so you can see why this document didn't load.
Reader Feedback: Page 1 of 1
Tweets by @BigDataExpo
Digital Transformation Blogs