Perl Cd Bookshelf [Electronic resources] نسخه متنی

XML Document Models and the Database

XML documents consist of text that conforms to a hierarchy or tree structure specified by a DTD or XML schema. In most cases you can easily store this hierarchical data in an optimal internal form using object-relational tables. All the existing and future internal applications can work with the information in the most efficient way possible. When you retrieve information, for sharing with partners or other applications, you can present the appropriate view of data and document content specific to the task at hand as integrated XML. Oracle Database’s 10g XMLType views enable you to present data in any number of “logical” combinations, hiding any details of their underlying physical storage. You can effectively transform the structure of one or more underlying tables into a more useful or more appropriate structure for the demands of a specific application. When you link views of information with other views of related information, they quite naturally form “trees” or “graphs” of related data. When you represent database information as XML, the previous related views provide the foundation for many different tree-structured XML documents.

Mapping DTDs to Database Schemas

Here, we offer a simple example of mapping a database table to an XML DTD of the following form:

<!ELEMENT table (rows)*>
<!ELEMENT rows (column1, column2, ...)>
<!ELEMENT column1 (#PCDATA)>
<!ELEMENT column2 (#PCDATA)>
...

However, a database provides even more capability than a DTD for expressing rules. Using DTDs, you cannot define type information other than numbers, strings, and IDs. The database schema defines type information and constraints, such as permissible value ranges. A database schema enables you to define relationships or dependencies. For example, your e-commerce business might receive orders as XML documents. By using a database, you can link customer and order information, and define a rule about not processing orders for closed accounts. In spite of the limitations in DTDs, mapping a database schema to a DTD presents the database as a virtual XML document to the tools that need XML documents as input.

A database consists of a schema associated with each database user. Each schema associated with a user is a collection of schema objects accessible to the user. While mapping a database scheme to a DTD, each user is mapped as a child element of the top-level element identified by the SID of the database instance. An element representing a user schema and its child elements uses a unique namespace to avoid conflicts with schema objects defined in other user schemas.

You can perform the following steps to generate a simple DTD from a relational schema:

For each table, create an element.

For each column in a table, create a PCDATA-only child element.

For each object or nested table column, create an ELEMENT-content only child element with attributes or nested columns as child elements.

For example, the following DTD corresponds to a simple database schema:

<!ELEMENT dbschema (sys, scott, ...)>
<!ATTLIST dbschema
xmlns CDATA #FIXED "http://www.oracle.com/xml/dbschema"
sid CDATA #REQUIRED>
<!ELEMENT scott (BookList, ...)>
<!ATTLIST scott
xmlns CDATA #FIXED "http://www.oracle.com/xml/dbschema/scott">
<!ELEMENT BookList (Book)*>
<!ATTLIST Book row_num CDATA #IMPLIED>
<!ELEMENT Book (Title, ISBN, Author, Publisher, (Review)*)>
...

Unfortunately, a number of drawbacks exist to mapping a database schema to a DTD. For example, there is no way to predict datatypes or column lengths definitively from the DTD. The solution to this problem is to use datatypes in XML documents using XML schemas.

Mapping XML Documents to a Database Schema

The format for DTDs is an existing worldwide standard and will likely exist and be improved upon for years. However, because of the inherent limitations of DTDs and the increasingly data-oriented role that XML is being asked to assume because of developments in e-business and e-commerce, the W3C standards body is promoting XML Schema, rather than attempting to push the current DTD standards any further. Using an XML schema, you can map the simple database table into an XML schema of the following form:

<schema targetNamespace="some NSURI">
<xsd:complexType name="table">
<xsd:sequence>
<xsd:element name="rows" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="rows" >
<xsd:sequence>
<xsd:element name="column1" type="xs:string"/>
<xsd:element name="column2" type="xs:date"/>
...
</xsd:sequence>
</xsd:complexType>
</xsd:schema>

Note that you can specify additional datatypes using the <xsd:element> attribute of the form type="xsd:datatype" where datatype is either an XML Schema simple datatype or a named type defined in the XML schema.

Supported Database Mappings

To transfer data between an XML document and a database, you must map a document structure defined by a DTD or an XML schema to a database schema and vice versa.

When mapping XML documents associated with a DTD or an XML schema to a database schema for the purpose of storing XML in Oracle XML DB, four basic strategies exist:

Map the complete XML document as a single, intact object, such as CLOBs

Map XML elements to object-relational tables and columns in the database schema

Map fragments of XML documents as CLOBs and the rest of the document as object-relational tables

Map XML documents to an Oracle XMLType

You can choose one of the previous approaches, depending on the structure of the XML document and operations performed by the application. You can also store the XML DTD or schema in the database to validate the XML documents.

XML Documents in CLOBs

Storing an intact XML document in a Character Large Object (CLOB) or Binary Large Object (BLOB) is a good strategy if the XML document contains static content that will only be updated by replacing the entire document. Examples include written text such as articles, advertisements, books, and legal contracts. Documents of this nature are known as document-centric and are delivered from the database as a whole. Storing this kind of document intact within the database gives you the advantages of an industry-proven database and its reliability over file system storage. If you choose to store an XML document outside the database, you can still use the database features to index, query, and efficiently retrieve the document through the use of Bfiles, URLs, and text-based indexing.

XML Documents as Object-Relational Data

If the XML document has a well-defined structure and contains data that is updatable or used in other ways, the document is data-centric. Typically, the XML document contains elements or attributes that have complex structures. Examples of this kind of document include sales orders, invoices, and airline flight schedules. Oracle Database 10g, with its object-relational extensions, has the capability to capture the structure of the data in the database using object types, object references, and collections. Two options exist for storing and preserving the structure of the XML data in an object-relational form:

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

Once stored in the object-relational form, the data can be easily updated, queried, rearranged, and reformatted as needed using SQL. The XML SQL Utility then provides the means to store an XML document by mapping it to the underlying object-relational storage and, conversely, provides the capability to retrieve the object-relational data as an XML document. If an XML document is structured, but the structure of the XML document is incompatible 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. Or, you can define object views corresponding to the various XML document structures and define INSTEAD OF triggers to perform the appropriate transformation and to update the base data.

XML Documents as Fragment Documents and Object-Relational Data

You can use Oracle Database 10g’s views to view and operate a combination of structured and unstructured XML data as a whole. Views enable you to construct an object on-the-fly by combining XML data stored in a variety of ways. So, you can store structured data (such as employee data, customer data, and so on) in one location within object-relational tables and store related unstructured data (such as descriptions and comments) within a CLOB. When you need to retrieve the data as a whole, you simply construct the structure from the various pieces of data using type constructors in the view’s SELECT statement. The XML SQL Utility then enables you to retrieve the constructed data from the view as a single XML document.

XML Documents as XMLTypes

Finally, you can store an XML document in an Oracle XML DB’s XMLType. XMLType supports searches and queries using XPATH-like syntax. It can be created as a database table, view, columns, and as the parameter and return type of SQL, PL/SQL, and Java functions. For example, the SYS_XMLGEN and SYS_XMLAGG Oracle SQL functions, which generate an XML document and aggregate a number of XML documents, respectively, can take as a parameter an XMLType object and return an XMLType object. These functions can be embedded in SQL queries as in a simple SELECT statement and return XML:

SELECT SYS_XMLGEN(book) FROM bookcatalog WHERE title LIKE '%ELLISON%';

and

SELECT SYS_XMLAGG(SYS_XMLGEN(book)).
getClobVal() book_list FROM bookcatalog GROUP BY title;

The first would return the XML of a book entry and the second would return a list of all the titles of the book entries.

Similarly, the DBMS_XMLGEN PL/SQL package converts the result set from SQL queries to an XML stored in an XMLType, as in

SELECT SYS_XMLAGG(SYS_XMLGEN(book)).
getClobVal() book_list FROM bookcatalog GROUP BY title

resulting in a listing of all the book titles in XML.

Table creation using XMLType columns and database manipulation language (DML) operations to insert, update, and delete values is allowed with this new data type, along with datatype member functions such as existsNode() and extract() that take arguments with XPATH-like syntax to return fragments of XML in the XMLType, such as

SELECT book.extract('//title/text()').getStringVal() FROM bookcatalog;

and

SELECT * FROM bookcatalog where book.existsNode('//book/title') != 0;