Perl Cd Bookshelf [Electronic resources]

What Is the Oracle XML Database?

As stated at the beginning of the chapter, the Oracle XML database refers to a collection of native XML technologies in the database server. However, the native XML support is only one part of the XML infrastructure in the Oracle database. The overall XML infrastructure in Oracle database provides both high-performance native XML support and an extensible platform on which users can build and deploy their own solutions.

The native XML DB functionality is available without having to run through a separate installation process. The extension platform is based on the Java Stored Procedures running in the Oracle JVM and the C-based external procedures. You can build customized XML solutions using the XML functionality in the Oracle XDK to compliment the existing native XML functionality. Figure 8-4 illustrates the overall XML infrastructure in the Oracle database.

Figure 8-4: Oracle database XML support

In the native XML engine, the XMLType tables and views provide the storage of XML data. The XML DB Repository provides an XML document repository that is optimized for handling XML documents, and the PL/SQL and SQL/XML functions allow XML operations on SQL data and XML content. The Java and C XML programming APIs in the Oracle XDK can be used to add functionality by building external procedures, such as creating a Java Stored Procedure for SAX stream-based XML processing, which is not available as part of the native XML support.

Exploring the native XML features is the focus in this section. The examples for building Java or C extensions using XDK will be included in later chapters to demonstrate the value of the entire Oracle XML infrastructure.

In the coming section, the following features will be demonstrated:

The native XMLType data type used to store and manage XML documents

XMLType functions and SQL/XML functions operating on XMLType data in SQL

XML DB Repository and its protocols interfaces including the FTP, HTTP, and WebDAV interfaces on XMLTypes

Oracle Text search on XMLTypes

Oracle Advanced Queuing extensions for XMLTypes

XMLType

XMLType is the native data type for storing XML data in the Oracle database. It is similar to the DATE data type in that you can use it to define table columns or use it as parameters, return values, or variables in the PL/SQL procedures.

XMLType provides a set of built-in member functions that operate on XML content, enabling you to create an XMLType instance from various resources, extract XML content, validate XML against XML Schemas, apply XSL transformations, and so on.

The following example creates a product table called product with an XMLType column called description for storing the product descriptions:

CREATE TABLE product(
id VARCHAR(10), 
name VARCHAR2(100), 
description XMLType);

You insert sample data into this table using the following SQL:

INSERT INTO product(id, name, description) 
VALUES('xdk', 'XML Developer''s Kit',
XMLTYPE('<DESCRIPTION><KEYWORD>xdk</KEYWORD> is a set of
standards-based utilities that help to build XML applications.
It contains XDK Java, C/C++ components.</DESCRIPTION>'));

In this example, the XMLType() construction function is used to create an XMLType instance from a string input.

As previously discussed, you can process XML by creating the PL/SQL procedures taking XMLType as parameters, variables, or return types. To demonstrate this, you create a PL/SQL procedure, in which the descriptions of each product are passed in as an XMLType parameter called p_desc. Using the UPDATEXML() SQL function, you update this product description by substituting all of the <KEYWORD> elements whose content equals p_id that are passed in by the p_name parameter:

CREATE OR REPLACE FUNCTION MYTRANSFORM (p_desc IN XMLTYPE,
p_id IN VARCHAR2, p_name IN VARCHAR2) RETURN XMLTYPE AS
v_result XMLType;
BEGIN
SELECT UPDATEXML(p_desc,
'//KEYWORD[text()='''||p_id||''']/text()',p_name) INTO v_result
FROM dual;
RETURN v_result;
END MYTRANSFORM;

After you have created the PL/SQL procedure, you can run the following SQL command:

SQL> set long 10000
SQL> SELECT MYTRANSFORM(description, id, name) FROM product;

You will see the following output:

MYTRANSFORM(DESCRIPTION,ID,NAME)
-----------------------------------------------------------------------
<DESCRIPTION> <KEYWORD>XML Developer&apos;s Kit</KEYWORD> is a set of
standards-based utilities that help to build XML applications. It
contains XDK Java, C/C++ components.</DESCRIPTION>

When storing XML in XMLTypes, there are several storage options. Basically, you can create XMLTypes as:

XML Schema–based XMLTypes Stored under an object-relational structure specified by a registered XML schema unless you specify the storage options when creating the objects.

Non XML Schema–based XMLTypes Stored in CLOBs.

The XML Schema defines how to “shred” the content of an XML document and store it as a set of SQL objects. For example, you can register an XML Schema as follows:

BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL=>'http://xmlns.oracle.com/xml/content.xsd',
SCHEMADOC=>'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="DESCRIPTION">
<xs:complexType mixed="true">
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="KEYWORD" type= "xs:string" maxOccurs="unbounded"/>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>',
LOCAL=>TRUE,
GENTYPES=>TRUE,
GENTABLES=>FALSE);
END;

In the Oracle XML database, each XML schema is registered under a unique URL so that XMLTypes can identify the XML schemas when referring to them. The preceding example uses the “Oracle XML DB Repository” section. You can look at the objects generated using the following SQL commands:

SQL> COLUMN object_name format a30
SQL> COLUMN object_type format a30
SQL> SELECT object_name, object_type FROM user_objects;

The objects created by the XML Schema registration are listed as follows:

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
DESCRIPTION163_T TYPE
KEYWORD164_COLL TYPE
PRODUCT TABLE
SYS_LOB0000042735C00004$$ LOB

You will see the SQL object types created for each complex type in the XML schema, such as the DESCRIPTION163_T and KEYWORD164_COLL. You can use the desc command to show the details of the object types:

SQL> desc DESCRIPTION163_T
DESCRIPTION163_T is NOT FINAL
Name Null? Type
----------------------------------------- -------- ------------------------
SYS_XDBPD$ XDB.XDB$RAW_LIST_T
KEYWORD KEYWORD164_COLL
SQL> desc KEYWORD164_COLL
KEYWORD164_COLL VARRAY(2147483647) OF VARCHAR2(4000)

A VARRAY of VARCHAR2(4000) is created for elements such as <KEYWORD> where its occurrence is unbounded. By default, its size is 2147483647 bytes! Not only is the default size of VARRAY too large for storing XML documents, the extra SYS_XDBPD$ column created to preserve the DOM fidelity of XML content may not be needed by your application. To customize these storage modes, you can annotate the XML schemas. How can you annotate the XML schema? What is the SYS_LOB0000042735C00004$$ column? We will answer these kinds of XMLType storage–related questions in Chapter 9.

By default, XMLTypes are stored in CLOBs unless you associate a registered XML schema URL with the XMLType columns or XMLType object tables. For example, in the previous product table, the description column is an XMLType in CLOB storage. Instead, you can define the column description as an XML Schema–based XMLType using the registered http://xmlns.oracle.com/xml/content.xsd schema as follows:

CREATE TABLE product(
id VARCHAR(10),
name VARCHAR2(100),
description XMLType)
XMLType COLUMN description
XMLSCHEMA "http://xmlns.oracle.com/xml/content.xsd"
ELEMENT "DESCRIPTION";

The description column uses the structured storage of XMLType. The following example shows that when inserting the same XML data into XML Schema–based XMLType columns, you need to use the XMLType.createSchemaBasedXML() function:

INSERT INTO product(id, name, description)
VALUES('xdk', 'XML Developer''s Kit', XMLTYPE('<DESCRIPTION>
<KEYWORD>xdk</KEYWORD> is a set of standards-based utilities that
helps to build<KEYWORD>XML</KEYWORD> applications. It contains XDK
Java, C/C++ Components.</DESCRIPTION>').CreateSchemaBasedXML(
'http://xmlns.oracle.com/xml/content.xsd'));

After XML is stored in XMLTypes, you can use the XMLType member functions to operate on the XML content. We will discuss this with more examples in Chapter 10. In this section, we just look at a simple example, which extracts XML nodes using the XMLType.extract() function. The following query extracts all <KEYWORD> elements in the product description.

SQL> SELECT p.description.extract('//KEYWORD') FROM product p;
P.DESCRIPTION.EXTRACT('//KEYWORD')
---------------------------------------------------------------------
<KEYWORD>xdk</KEYWORD>
<KEYWORD>XML</KEYWORD>

XMLType Views

XMLType can be used to define views, called XMLType Views. For example, you can create an XMLType view based on the employees table in the HR user schema:

CREATE OR REPLACE VIEW employee_vw AS
SELECT XMLELEMENT("Employee",
XMLATTRIBUTES(employee_id AS "empno"),
XMLFOREST(first_name, last_name, job_id))AS result
FROM hr.employees;

You may not be familiar with the SQL/XML functions used in the example. Do not worry. We will discuss these in the next section. Simply query the view, and you will see the XML content that is returned:

SQL> SELECT * FROM employee_vw WHERE ROWNUM<2; 
RESULT
----------------------------------------------------------------------------
<Employee empno="100">
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
<JOB_ID>AD_PRES</JOB_ID>
</Employee>

From this example, you can see that the XMLType view provides an option to wrap up existing object-relational data in XML, which then can be used for both Web publishing and data exchange. Additionally, XMLType views can serve as an XML interface for XML-centric processing, such as XPath-based content navigation and updates. You can build XMLType views based on XML schemas. The major difference between these XML Schema–based XMLType views and XMLType views that are not based on XML schemas is that the XML Schema validation can occur, which brings rich data-type constraints and XPath query optimization.

SQL/XML Processing

SQL is the standard for efficiently managing relational data. The XML extensions of SQL in Oracle XML Database allow SQL operations previously limited to relational data to operate on XML data as well. Table 8-2 outlines the overall functionality of SQL/XML and Oracle extension support in Oracle Database 10g.

By using Oracle SQL extensions and the SQL/XML functions for XML processing, you can easily leverage the relational data model and XML data model in one SQL query. This gives you the maximum flexibility to solve business problems. In the previous example, the SQL/XML functions are used to create XMLType views. We will use additional SQL operators and functions in later chapters, and discuss the technical details of how to use these SQL functions in Chapter 10.