| Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2) Part Number A96620-02 |
|
|
View PDF |
| Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2) Part Number A96620-02 |
|
|
View PDF |
This chapter describes where and how you can use Oracle XML DB. It discusses and includes examples on common Oracle XML DB usage scenarios including XMLType data storage and access, updating and validating your data, and why it helps to understand XPath and XML Schema. It provides you with ideas for how you can use the Repository to store, access, and manipulate database data using standard protocols from a variety of clients.
The chapter also discusses how you can define a default XML table for storing XML schema-based documents and using XDBUriType to access non-schema-based content.
It contains the following sections:
See Also:
for further examples of where and how you can use Oracle XML DB. |
When storing XML documents in Oracle9i database you can use a number of approaches, including:
CLOB or VARCHAR column. Again in this scenario the database has no idea that it is managing XML content, but you can programmatically use XDK to perform XML operations.XMLType datatype. Two options are available in this scenario.
XMLType column.XMLType table.Both these options mean that the database is aware that it is managing XML content. Selecting this approach provides you with a number of significant advantages, as the database provides a set of features that make it possible to process XML content efficiently.
CREATE TABLE Example1 ( KEYVALUE varchar2(10) primary key, XMLCOLUMN xmltype );
CREATE TABLE XMLTABLE OF XMLType;
To store an XML document in an XMLType table or column the XML document must first be converted into an XMLType instance. This is done using the different constructors provided by the XMLType datatype. For example, given a PL/SQL function called getCLOBDocument():
create or replace function getClobDocument( filename in varchar2, charset in varchar2 default NULL) return CLOB deterministic is file bfile := bfilename('DIR',filename); charContent CLOB := ' '; targetFile bfile; lang_ctx number := DBMS_LOB.default_lang_ctx; charset_id number := 0; src_offset number := 1 ; dst_offset number := 1 ; warning number; begin if charset is not null then charset_id := NLS_CHARSET_ID(charset); end if; targetFile := file; DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly); DBMS_LOB.LOADCLOBFROMFILE(charContent, targetFile, DBMS_LOB.getLength(targetFile), src_offset, dst_offset, charset_id, lang_ctx,warning); DBMS_LOB.fileclose(targetFile); return charContent; end; / -- create XMLDIR directory -- connect system/manager -- create directory XMLDIR as '<location_of_xmlfiles_on_server>'; -- grant read on directory xmldir to public with grant option; -- you can use getCLOBDocument() to generate a CLOB from a file containin -- an XML document. For example, the following statement inserts a row into the -- XMLType table Example2 created earlier: INSERT INTO XMLTABLE VALUES(XMLTYPE(getCLOBDocument('purchaseorder.xml')));
Note the use of parameter, "charset". This is used to identify the character set of the designated file. If omitted, the default character set id of the current database is used.
For example, if a file, invoice.xml uses one of the Korean character sets, KO16KSC5601, it can be loaded into XMLType table, XMLDOC as follows:
insert into xmldoc values(xmltype(getClobDocument('invoice.xml','KO16KSC5601')));
The following example uses the UTF8 file format:
insert into xmldoc values(xmltype(getClobDocument('invoice.xml','UTF8')));
In the last example both the database and the file use the same character set such as UTF8:
insert into xmldoc values(xmltype(getClobDocument('invoice.xml')));
|
Note: Oracle XML DB can handle multibyte characters as long as the client character set is the same as the database character set. |
Once a collection of XML documents have been stored as XMLType tables or columns the next step is to be able to retrieve them. When working with a collection of XML documents you have two fundamental tasks to perform:
Oracle9i database and XMLType datatype provide a number of functions that make it easy to perform these tasks. These functions make use of the W3C XPath recommendation to navigate across and within a collection of XML documents.
| See Also:
Appendix C, "XPath and Namespace Primer" for an introduction to the W3C XPath Recommendation. |
A number of the functions provided by the Oracle XML DB are based on the W3C XPath recommendation. XPath traverses nested XML elements by your specifying the elements to navigate through with a slash-separated list of element and attribute names. By using XPath to define queries within and across XML documents. With Oracle XML DB you can express hierarchical queries against XML documents in a familiar, standards compliant manner.
The primary use of XPath in Oracle XML DB is in conjunction with the extract(), extractValue(), and existsNode() functions.
The existsNode() function evaluates whether or not a given document contains a node which matches a W3C XPath expression. The existsNode() function returns true (1) if the document contains the node specified by the XPath expression supplied to the function. The functionality provided by the existsNode() function is also available through the XMLType datatype existNode() method.
Examples in this section are based on the following PurchaseOrder XML document:
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/xdb/po.xsd"> <Reference>ADAMS-20011127121040988PST</Reference> <Actions> <Action> <User>SCOTT</User> <Date>2002-03-31</Date> </Action> </Actions> <Reject/> <Requestor>Julie P. Adams</Requestor> <User>ADAMS</User> <CostCenter>R20</CostCenter> <ShippingInstructions> <name>Julie P. Adams</name> <address>Redwood Shores, CA 94065</address> <telephone>650 506 7300</telephone> </ShippingInstructions> <SpecialInstructions>Ground</SpecialInstructions> <LineItems> <LineItem ItemNumber="1"> <Description>The Ruling Class</Description> <Part Id="715515012423" UnitPrice="39.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="2"> <Description>Diabolique</Description> <Part Id="037429135020" UnitPrice="29.95" Quantity="3"/> </LineItem> <LineItem ItemNumber="3"> <Description>8 1/2</Description> <Part Id="037429135624" UnitPrice="39.95" Quantity="4"/> </LineItem> </LineItems> </PurchaseOrder>
The existsNode() syntax is shown in Figure 3-1.
Given this sample XML document, the following existsNode() operators return true (1).
SELECT existsNode(value(X),'/PurchaseOrder/Reference') FROM XMLTABLE X; SELECT existsNode(value(X), '/PurchaseOrder[Reference="ADAMS-20011127121040988PST"]') FROM XMLTABLE X; SELECT existsNode(value(X), '/PurchaseOrder/LineItems/LineItem[2]/Part[@Id="037429135020"]') FROM XMLTABLE X; SELECT existsNode(value(X), '/PurchaseOrder/LineItems/LineItem[Description="8 1/2"]') FROM XMLTABLE X;
The following existsNode() operations do not find a node that matches the XPath expression and all return false(0):
SELECT existsNode(value(X),'/PurchaseOrder/UserName') FROM XMLTABLE X; SELECT existsNode(value(X), '/PurchaseOrder[Reference="ADAMS-XXXXXXXXXXXXXXXXXXXX"]') FROM XMLTABLE X; SELECT existsNode(value(X), '/PurchaseOrder/LineItems/LineItem[3]/Part[@Id="037429135020"]') FROM XMLTABLE X; SELECT existsNode(value(X), '/PurchaseOrder/LineItems/LineItem[Description="Snow White"]') FROM XMLTABLE X;
The most common use for existsNode() is in the WHERE clause of SQL SELECT, UPDATE, or DELETE statements. In this situation the XPath expression passed to the existsNode() function is used to determine which of the XML documents stored in the table will be processed by the SQL statement.
SELECT count(*) FROM XMLTABLE x WHERE existsNode(value(x),'/PurchaseOrder[User="ADAMS"]') = 1; DELETE FROM XMLTABLE x WHERE existsNode(value(x),'/PurchaseOrder[User="ADAMS"]') = 1; commit;
The extractValue() function is used to return the value of a text node or attribute associated with an XPath Expression from an XML document stored as an XMLType. It returns a scalar data type.
The extractValue() syntax is shown in Figure 3-2.
The following are examples of extractValue():
SELECT extractValue(value(x),'/PurchaseOrder/Reference') FROM XMLTABLE X;
Returns the following:
EXTRACTVALUE(VALUE(X),'/PURCHASEORDER/REFERENCE') ------------------------------------------------------------------------ ADAMS-20011127121040988PST SELECT extractValue(value(x), '/PurchaseOrder/LineItems/LineItem[2]/Part/@Id') FROM XMLTABLE X;
Returns the following:
EXTRACTVALUE(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM[2]/PART/@ID') ----------------------------------------------------------------------- 037429135020
extractValue() can only return a the value of a single node or attribute value. For instance the following example shows an invalid use of extractValue(). In the first example the XPath expression matches three nodes in the document, in the second example the Xpath expression identifies a nodetree, not a text node or attribute value.
SELECT extractValue(value(X), '/PurchaseOrder/LineItems/LineItem/Description') FROM XMLTABLE X; -- FROM XMLTABLE X; -- * -- ERROR at line 3: -- ORA-19025: EXTRACTVALUE returns value of only one node SELECT extractValue(value(X), '/PurchaseOrder/LineItems/LineItem[1]') FROM XMLTABLE X; -- FROM XMLTABLE X -- * -- ERROR at line 3: -- ORA-19025: EXTRACTVALUE returns value of only one node
extractValue() can also be used in the WHERE clause of a SELECT, UPDATE, or DELETE statement. This makes it possible to perform joins between XMLType tables or tables containing XMLType columns and other relational tables or XMLType tables. The following query shows you how to use extractValue() in both the SELECT list and the WHERE clause:
SELECT extractValue(value(x),'/PurchaseOrder/Reference') FROM XMLTABLE X, SCOTT.EMP WHERE extractValue(value(x),'/PurchaseOrder/User') = EMP.ENAME AND EMP.EMPNO = 7876; -- This returns: -- EXTRACTVALUE(VALUE(X),'/PURCHASEORDER/REFERENCE') -- -------------------------------------------------- -- ADAMS-20011127121040988PST
The extract() syntax is shown in Figure 3-3.
extract() is used when the XPath expression will result in a collection of nodes being returned. The nodes are returned as an instance of XMLType. The results of extract() can be either a Document or a DocumentFragment. The functionality of extract is also available through the XMLType datatype's extract() method.
The following extract() statement returns an XMLType that contains an XML document fragment containing occurrences of the Description node. These match the specified XPath expression shown.
set long 20000 SELECT extract(value(X), '/PurchaseOrder/LineItems/LineItem/Description') FROM XMLTABLE X; -- This returns: -- EXTRACT(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM/DESCRIPTION') -- ------------------------------------------------------------------ -- <Description>The Ruling Class</Description> -- <Description>Diabolique</Description> -- <Description>8 1/2</Description>
In this example extract() returns the node tree that matches the specified XPath expression:
SELECT extract(value(X), '/PurchaseOrder/LineItems/LineItem[1]') FROM XMLTABLE X;
This returns:
EXTRACT(VALUE(X),'/PURCHASEORDER/LINEITEMS/LINEITEM[1]') ------------------------------------------------------------------------- <LineItem ItemNumber="1"> <Description>The Ruling Class</Description> <Part Id="715515012423" UnitPrice="39.95" Quantity="2"/> </LineItem>
The XMLSequence() syntax is shown in Figure 3-4.
An XML document fragment can be converted into a set of XMLTypes using the XMLSequence() function. XMLSequence() takes an XMLType containing a document fragment and returns a collection of XMLType objects. The collection will contain one XMLType for each root level node in the fragment. The collection can then be converted into a set of rows using the SQL TABLE function.
The following example shows how to use XMLSequence() and Table() to extract the set of Description nodes from the purchaseorder document.
set long 10000 set feedback on SELECT extractValue(value(t),'/Description') FROM XMLTABLE X, TABLE ( xmlsequence ( extract(value(X), '/PurchaseOrder/LineItems/LineItem/Description') ) ) t;
This returns:
EXTRACTVALUE(VALUE(T),'/DESCRIPTION') ------------------------------------------------------------------------- The Ruling Class Diabolique 8 1/2
The updateXML() syntax is shown in Figure 3-5.
You can update XML documents using the updateXML() function. updateXML() updates an attribute value, node, text node, or node tree. The target for the update operation is identified using an XPath expression. The following examples show how you can use updateXML() to modify the contents of an XML Document stored as an XMLType.
This example uses updateXML() to update the value of the text node identified by the XPath expression `/PurchaseOrder/Reference':
UPDATE XMLTABLE t SET value(t) = updateXML(value(t), '/PurchaseOrder/Reference/text()', 'MILLER-200203311200000000PST') WHERE existsNode(value(t), '/PurchaseOrder[Reference="ADAMS-20011127121040988PST"]') = 1;
This returns:
1 row updated. SELECT value(t) FROM XMLTABLE t;
This returns:
VALUE(T) ------------------------------------------------------------------------- <PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.oracle.com/xdb/po.xsd"> <Reference>MILLER-200203311200000000PST</Reference> ... </PurchaseOrder>
In this example updateXML() replaces the contents of the node tree associated with the element identified by the XPath expression `/PurchaseOrders/LineItems/LineItem[2]'.
|
Note: In this example, since the replacement value is a Node tree, the third argument to the |
UPDATE XMLTABLE t SET value(t) = updateXML(value(t), '/PurchaseOrder/LineItems/LineItem[2]', xmltype('<LineItem ItemNumber="4"> <Description>Andrei Rublev</Description> <Part Id="715515009928" UnitPrice="39.95" Quantity="2"/> </LineItem>' ) ) WHERE existsNode(value(t), '/PurchaseOrder[Reference="MILLER-200203311200000000PST"]' ) = 1;
This returns:
1 row updated. SELECT value(t) FROM XMLTABLE t;
And this returns:
VALUE(T) ------------------------------------------------------------------------ <PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames paceSchemaLocation="http://www.oracle.com/xdb/po.xsd"> <Reference>MILLER-200203311200000000PST</Reference> ... <LineItems> <LineItem ItemNumber="1"> <Description>The Ruling Class</Description> <Part Id="715515012423" UnitPrice="39.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="4"> <Description>Andrei Rublev</Description> <Part Id="715515009928" UnitPrice="39.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="3"> <Description>8 1/2</Description> <Part Id="037429135624" UnitPrice="39.95" Quantity="4"/> </LineItem> </LineItems> </PurchaseOrder>
The W3C XSLT Recommendation defines an XML language for specifying how to transform XML documents from one form to another. Transformation can include mapping from one XML schema to another or mapping from XML to some other format such as HTML or WML.
| See Also:
Appendix D, "XSLT Primer" for an introduction to the W3C XSL and XSLT recommendations. |
The following example, PurchaseOrder.xsl, is an example fragment of an XSL stylesheet:
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <xsl:template match="/"> <html> <head/> <body bgcolor="#003333" text="#FFFFCC" link="#FFCC00" vlink="#66CC99" alink="#669999"> <FONT FACE="Arial, Helvetica, sans-serif"> <xsl:for-each select="PurchaseOrder"/> <xsl:for-each select="PurchaseOrder"> <center> <span style="font-family:Arial; font-weight:bold"> <FONT COLOR="#FF0000"> <B>Purchase Order </B> </FONT> </span> </center> <br/> ... <FONT FACE="Arial, Helvetica, sans-serif" COLOR="#000000"> <xsl:for-each select="Part"> <xsl:value-of select="@Quantity*@UnitPrice"/> </xsl:for-each> </FONT> </td> </tr> </tbody> </xsl:for-each> </xsl:for-each> </table> </xsl:for-each> </FONT> </body> </html> </xsl:template> </xsl:stylesheet>
| See Also:
Appendix D, "XSLT Primer" for the full listing of this XSL stylesheet. |
Oracle XML DB complies with the W3C XSL/XSLT recommendation by supporting XSLT transformations in the database. In Oracle XML DB, XSLT transformations can be performed using either of the following:
Since XSL stylesheets are valid XML documents both approaches apply when the XSL stylesheets are provided as instances of the XMLType datatype. The results of the XSL transformation are also returned as an XMLType.
Because the transformation takes place close to the data, Oracle XML DB can optimize features such as memory usage, I/O operations, and network traffic required to perform the transformation.
The following example shows how transform() can apply XSLT to an XSL stylesheet, PurchaseOrder.xsl, to transform the PurchaseOrder.xml document:
SELECT value(t).transform(xmltype(getDocument('purchaseOrder.xsl'))) from XMLTABLE t where existsNode(value(t), '/PurchaseOrder[Reference="MILLER-200203311200000000PST"]' ) = 1;
This returns:
VALUE(T).TRANSFORM(XMLTYPE(GETDOCUMENT('PURCHASEORDER.XSL'))) ------------------------------------------------------------------------- <html> <head/> <body bgcolor="#003333" text="#FFFFCC" link="#FFCC00" vlink="#66CC99" alink="# 669999"> <FONT FACE="Arial, Helvetica, sans-serif"> <center> ... </FONT> </body> </html>
Since the transformed document using XSLT is expected as in instance of XMLType, the source could easily be a database table.
The following describes additional XMLType methods:
createXML(). A static method for creating an XMLType instance. Different signatures allow the XMLType to created from an number of different sources containing an XML document. Largely replaced by the XMLType constructor in Oracle9i Release 2 (9.2).isFragment(). Returns true (1) if the XMLType contains a document fragment. A document fragment is an XML document without a Root Node. Document fragments are typically generated using the extract() function and method.getClobVal(). Returns a CLOB containing an XML document based on the contents of the XMLType.getRootElement(). Returns the name of the root element of the XML document contained in the XMLType.getNameSpace(). Returns the name of the root element of the XML document contained in the XMLType.XML Schema provides a standardized way of defining what the expected contents of a set of XML documents should be. An XML schema is a an XML document that defines metadata. This metadata specifies what the member contents of the document class should be. The members of a document class can be referred to as instance documents.
Since an XML schema definition is simply an XML document that conforms to the class defined by the XML Schema http://www.w3.org/2001/XMLSchema, XML schemas can be authored using a simple text editor, such as Notepad, vi, a schema-aware editor, such as the XML editor included with the Oracle9i JDeveloper tool, or an explicit XML schema authoring tool, such as XMLSpy from Altova Corporation. The advantage of using a tool such as XMLSpy, is that these tools allow the XML schema to be developed using an intuitive, graphical editor which hides much of the details of the XML schema definition from the developer.
The following example PurchaseOrder.xsd, is a standard W3C XML Schema example fragment, in its native form, as an XML Document:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:complexType name="ActionsType" > <xs:sequence> <xs:element name="Action" maxOccurs="4" > <xs:complexType > <xs:sequence> <xs:element ref="User"/> <xs:element ref="Date"/> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> <xs:complexType name="RejectType" > <xs:all> <xs:element ref="User" minOccurs="0"/> <xs:element ref="Date" minOccurs="0"/> <xs:element ref="Comments" minOccurs="0"/> </xs:all> </xs:complexType> <xs:complexType name="ShippingInstructionsType" > <xs:sequence> <xs:element ref="name"/> <xs:element ref="address"/> <xs:element ref="telephone"/> </xs:sequence> ... .... <xs:complexType> <xs:attribute name="Id" > <xs:simpleType> <xs:restriction base="xs:string"> <xs:minLength value="12"/> <xs:maxLength value="14"/> </xs:restriction> </xs:simpleType> </xs:attribute> <xs:attribute name="Quantity" type="money"/> <xs:attribute name="UnitPrice" type="quantity"/> </xs:complexType> </xs:element> </xs:schema>
| See Also:
Appendix B, "XML Schema Primer" for the detailed listing of |
Oracle XML DB supports the use of the W3C XML Schema in two ways.
To use a W3C XML Schema with Oracle XML DB, the XML schema document has to be registered with the database. Once an XML schema has been registered XMLType tables and columns can be created which are bound to the schema.
To register an XML schema you must provide two items. The first is the XMLSchema document, the second is the URL which will be used by XML documents which claim to conform to this Schema. This URL will be provided in the root element of the instance document using either the noNamespaceSchemaLocation attribute or schemaLocation attribute as defined in the W3C XML Schema recommendation
XML schemas are registered using methods provided by PL/SQL package DBMS_XMLSCHEMA. Schemas can be registered as global or local schemas. See Chapter 5, "Structured Mapping of XMLType" for a discussion of the differences between Global and Local Schemas.
Oracle XML DB provides a number of options for automatically generating default database objects and Java classes as part of the schema registration process. Some of these options are discussed later in this section.
The following example shows how to register the preceding PurchaseOrder.xsd XML schema as a local XML schema using the registerSchema() method.
begin dbms_xmlschema.registerSchema( 'http://www.oracle.com/xsd/purchaseOrder.xsd', getDocument('PurchaseOrder.xsd'), TRUE, TRUE, FALSE, FALSE ); end; / --This returns: -- PL/SQL procedure successfully completed.
The registerSchema() procedure causes Oracle XML DB to perform the following operations:
Once the XML schema has been registered with Oracle XML DB, it can be referenced when defining tables that contain XMLType columns, or creating XMLType tables.
This example shows how to create an XMLType table which can only contain XML Documents that conform to the definition of the PurchaseOrder element in the XML schema registered at `http://www.oracle.com/xsd/purchaseorder.xsd'.
CREATE TABLE XML_PURCHASEORDER of XMLType XMLSCHEMA "http://www.oracle.com/xsd/purchaseOrder.xsd" ELEMENT "PurchaseOrder";
This results in:
Table created. DESCRIBE XML_PURCHASEORDER
Returns the following:
Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE of SYS.XMLTYPE(XMLSchema "http://www.oracle.com/xsd/purchaseOrder.xsd" Element "PurchaseOrder") STORAGE Object-relational TYPE "PurchaseOrder538_T"
Oracle XML DB must recognize that the XML document inserted into an XML schema-based table or column is a valid member of the class of documents defined by the XML schema. The XML document must correctly identify the XML schema or XML schemas it is associated with.
This means that XML schema, for each namespace used in the document, must be identified by adding the appropriate attributes to the opening tag for the root element of the document. These attributes are defined by W3C XML Schema recommendation and are part of the W3C XMLSchema-Instance namespace. Consequently in order to define these attributes the document must first declare the XMLSchema-instance namespace. This namespace is declared as follows: xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance:
Once the XMLSchema-instance namespace has been declared and given a namespace prefix the attributes that identify the XML schema can be added to the root element of the instance document. A given document can be associated with one or more XML schemas. In the preceding example, the namespace prefix for the XMLSchema-instance namespace was defined as xsi.
The XML schema associated with the unqualified elements is defined using the attribute noNamespaceSchemaLocation. In the case of the PurchaseOrder.xsd XML schema, the correct definition would be as follows:
<PurchaseOrder xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xsi:noNamespaceSchemaLocation="http://www.oracle.com/xsd/purchaseOrder.xsd">
If the XML document uses multiple namespaces then each namespace needs to be identified by a schemaLocation attribute. For example, assuming that the Purchaseorder document used the namespace PurchaseOrder, and the PurchaseOrder namespace is given the prefix po. The definition of the root element of a PurchaseOrder document would then be as follows:
<po:PurchaseOrder xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xmlns:po="PurchaseOrder"
xsi:schemaLocation="PurchaseOrder http://www.oracle.com/xsd/purchaseOrder.xsd">
By default Oracle XML DB performs a minimum amount of validation when a storing an instance document. This minimal validation ensures that the structure of the XML document conforms to the structure specified in the XML schema.
The following example shows what happens when an attempt is made to insert an XML Document containing an invoice into a XMLType table that is defined as storing documents which conform to the PurchaseOrder Schema
INSERT INTO XML_PURCHASEORDER values (xmltype(getDocument('Invoice.xml'))) values (xmltype(getDocument('Invoice.xml'))) *
This returns:
ERROR at line 2: ORA-19007: Schema and element do not match
The reason for not performing full instance validation automatically is based on the assumption that, in the majority of cases it is likely that schema based validation will have been performed prior to attempting to insert the XML document into the database.
In situations where this is not the case, full instance validation can be enabled using one of the following approaches:
This example shows how to use a CHECK constraint to an XMLType table and the result of attempting to insert an invalid document into the table:
ALTER TABLE XML_PURCHASEORDER add constraint VALID_PURCHASEORDER check (XMLIsValid(sys_nc_rowinfo$)=1); -- This returns: -- Table altered INSERT INTO XML_PURCHASEORDER values (xmltype(getDocument('InvalidPurchaseOrder.xml'))); INSERT INTO XML_PURCHASEORDER; * -- This returns: -- ERROR at line 1: -- ORA-02290: check constraint (DOC92.VALID_PURCHASEORDER) violated
The next example shows how to use a BEFORE INSERT trigger to validate that the data being inserted into the XMLType table conforms to the specified schema
CREATE TRIGGER VALIDATE_PURCHASEORDER before insert on XML_PURCHASEORDER for each row declare XMLDATA xmltype; begin XMLDATA := :new.sys_nc_rowinfo$; xmltype.schemavalidate(XMLDATA); end; / -- This returns: -- Trigger created. insert into XML_PURCHASEORDER values (xmltype(getDocument('InvalidPurchaseOrder.xml'))); -- values (xmltype(getDocument('InvalidPurchaseOrder.xml'))) -- * -- ERROR at line 2: -- ORA-31154: invalid XML document -- ORA-19202: Error occurred in XML processing -- LSX-00213: only 0 occurrences of particle "User", minimum is 1 -- ORA-06512: at "SYS.XMLTYPE", line 0 -- ORA-06512: at "DOC92.VALIDATE_PURCHASEORDER", line 5 -- ORA-04088: error during execution of trigger 'DOC92.VALIDATE_PURCHASEORDER'
As can be seen both approaches ensure that only valid XML documents can be stored in the XMLType table:
TABLE constraint approach's advantage is that it is simpler to code. Its disadvantage is that, since it is based on the isSchemaValid() method, it can only indicate whether or not the instance document is valid. When the instance document is not valid it cannot give any information as to why a document is invalid.BEFORE INSERT trigger requires a little more coding. Its advantage is that it is based on the schemaValidate() method. This means that when the instance document is not valid it can provide information about what was wrong with the instance document. It also has the advantage of allowing the trigger to take corrective action when appropriate.When designing an Oracle XML DB application you must first decide whether the XMLType columns and table will be stored using structured or unstructured storage techniques.
Table 3-1 compares using structured and structured storage to store XML.
Oracle XML DB ensures that all Data Manipulation Language (DML) operations based on Oracle XML DB functions return consistent results. By abstracting the storage model through the use of the XMLType datatype, and providing a set of operators that use XPath to perform operations against XML documents, Oracle XML DB makes it possible for you to switch between structured and unstructured storage, and to experiment with different forms of structured storage without affecting the application.
To preserve DOM fidelity a system must ensure that a DOM generated from the stored representation of an XML Document is identical to a DOM generated from the original XML document. Preserving DOM integrity ensures that none of the information contained in the XML Document is lost as a result of storing it.
The problem with maintaining DOM integrity is that an XML document can contain a lot of information in addition to the data contained in element and attribute values. Some of this information is explicitly provided, using Comments and Processing Instructions. Other information can be implicitly provided, such as:
One of the common problems application developers face when using a traditional relational model to manage the contents of XML documents is how to preserve this information. Table 3-2 compares DOM fidelity in structured and unstructured storage:
Logically, an XML document consists of a collection of elements and attributes. Elements can be either of the following:
An XML schema defines the set of elements and attributes that can exist in a particular class of XML document and defines the relationships between them.
During XML schema registration, Oracle XML DB generates an SQL Object Type for each complexType defined in the XML schema. The definition of the SQL object mirrors the definition of the complexType.
Each child element and attribute defined by the complexType maps to an attribute of the SQL object type.
complexType is itself a complexType, the datatype of the corresponding SQL attribute will be the appropriate SQL type.simpleType or attribute, based on one of the scalar datatypes defined by the W3C XML Schema recommendation, then the datatype of the corresponding SQL attribute will be the appropriate primitive SQL data type.By default SQL Objects generated when an XML schema is registered are given system-generated names. However, with Oracle XML DB you can specify the names of SQL objects by annotating the schema. To annotate an XML schema, you must first include the Oracle XML DB namespace in the XMLSchema tag, defined as:
http://xmlns.oracle.com/xdb
Hence an XML schema using Oracle XML DB annotations, must contain the following attributes in the XMLSchema tag:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" > ... </xs:schema>
Once Oracle XML DB namespace has been defined, the annotations defined by Oracle XML DB can be used.
This example uses xdb:SQLType to define the name of the SQL object generated from complexType PurchaseOrder, as XML_PURCHASEORDER_TYPE.
<xs:element name="PurchaseOrder"> <xs:complexType type="PurchaseOrderType" xdb:SQLType="XML_PURCHASEORDER_TYPE"> <xs:sequence> <xs:element ref="Reference"/> <xs:element name="Actions" type="ActionsType"/> <xs:element name="Reject" type="RejectType" minOccurs="0"/> <xs:element ref="Requestor"/> <xs:element ref="User"/> <xs:element ref="CostCenter"/> <xs:element name="ShippingInstructions" type="ShippingInstructionsType"/> <xs:element ref="SpecialInstructions"/> <xs:element name="LineItems" type="LineItemsType"/> </xs:sequence> </xs:complexType> </xs:element>
So executing the following statement:
DESCRIBE XML_PURCHASEORDER_TYPE XML_PURCHASEORDER_TYPE is NOT FINAL;
Returns the following structure:
Name Null? Type ------------------------------ -------- ---------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T Reference VARCHAR2(26) Actions XML_ACTIONS_TYPE Reject XML_REJECTION_TYPE Requestor VARCHAR2(128) User VARCHAR2(10) CostCenter VARCHAR2(4) ShippingInstructions XML_SHIPPINGINSTRUCTIONS_TYPE SpecialInstructions VARCHAR2(2048) LineItems XML_LINEITEMS_TYPE
|
Note: In the preceding example, |
Oracle XML DB uses a predefined algorithm to generate valid SQL names from the names of the XML elements, attributes, and types defined in the XML schema. The xdb:SQLName annotation can be used to override the default algorithm and supply explicit names for these items.
Oracle XML DB also provides a default mapping between scalar datatypes defined by the XML Schema recommendation and the primitive datatype defined by SQL. Where possible the size of the SQL datatype is derived from restrictions defined for the XML datatype. If required, the xdb:SQLType annotation can be used to override this default mapping:
This example shows how to override the name and type used for the SpecialInstructions element and the effect these changes have on the generated SQL Object type.
|
Note: The override for the name of the |
<xs:element name="SpecialInstructions" xdb:SQLType="CLOB" > <xs:simpleType> <xs:restriction base="xs:string"> <xs:minLength value="0"/> <xs:maxLength value="2048"/> </xs:restriction> </xs:simpleType> </xs:element> <xs:element name="PurchaseOrder"> <xs:complexType type="PurchaseOrderType" xdb:SQLType="XML_PURCHASEORDER_TYPE"> <xs:sequence> <xs:element ref="Reference"/> <xs:element name="Actions" type="ActionsType"/> <xs:element name="Reject" type="RejectType" minOccurs="0"/> <xs:element ref="Requestor"/> <xs:element ref="User"/> <xs:element ref="CostCenter"/> <xs:element name="ShippingInstructions" type="ShippingInstructionsType"/> <xs:element ref="SpecialInstructions" xdb:SQLName="SPECINST"/> <xs:element name="LineItems" type="LineItemsType"/> </xs:sequence> </xs:complexType> </xs:element>
On executing the following statement:
DESCRIBE XML_PURCHASEORDER_TYPE XML_PURCHASEORDER_TYPE is NOT FINAL
The following structure is returned:
Name Null? Type ------------------------------ -------- ---------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T Reference VARCHAR2(26) Actions XML_ACTIONS_TYPE Reject XML_REJECTION_TYPE Requestor VARCHAR2(128) User VARCHAR2(10) CostCenter VARCHAR2(4) ShippingInstructions XML_SHIPPINGINSTRUCTIONS_TYPE SPECINST CLOB LineItems XML_LINEITEMS_TYPE
One issue you must consider when selecting structured storage, is what techniques to use to manage Collections. Different approaches are available and each approach offers different benefits. Generally, you can handle Collections in five ways:
complexType is defined with xdb:SQLType="CLOB" then the type, and all child elements are stored using unstructured storage techniquescomplexType which occurs more than once, the members of the collection are stored as a set of serialized objects in-line as part of the SQL object for the parent element. You cannot create B*Tree indexes on elements or attributes which are part of collectionsetid (set identifier) value which is used to associate with the corresponding nested table rows.XMLType table. Each member of the collection is stored as a row in the table. The Parent SQL object contains an array of refs which point to the rows in the child table which belong to this parent. All data is XMLType.
XMLType table. An link table is created which cross references which member in the child table are linked to which members of the parent. Table. All data is visible as XMLTypes. Possible to link from the child back to the parent. Problems with creating multiple XMLType columns based on the Schema.
In addition to schema-validation, structured storage makes it possible to introduce traditional relational constraints on to XMLType columns and Tables. With database integrity checking you can perform instance validation beyond what is achievable with XML Schema-based validation.
The W3C XML Schema Recommendation only allows for validation based on cross-referencing of values with an instance document. With database integrity checking you can enforce other kinds of validation, such as enforcing the uniqueness of a element or attribute across a collection of documents, or validating the value of a element or attribute against information stored elsewhere in the database.
The following example shows how you can introduce a Unique and Referential Constraint on the PurchaseOrder table.
XMLDATA.SQLAttributeName alter table XML_PURCHASEORDER add constraint REFERENCE_IS_UNQIUE -- unique(extractValue('/PurchaseOrder/Reference')) unique (xmldata."Reference"); alter table XML_PURCHASEORDER add constraint USER_IS_VALID -- foreign key extractValue('/PurchaseOrder/User') references SCOTT.EMP(ENAME) foreign key (xmldata."User") references SCOTT.EMP(ENAME);
As can be seen, when an attempt is made to insert an XML Document that contains a duplicate value for the element /PurchaseOrder/Reference into the table, the database detects that the insert would violate the unique constraint, and raises the appropriate error.
insert into xml_purchaseorder values ( xmltype(getDocument('ADAMS-20011127121040988PST.xml')) );
This returns:
1 row created. insert into xml_purchaseorder values ( xmltype(getDocument('ADAMS-20011127121040988PST.xml')) ); insert into xml_purchaseorder values ( *
This returns:
ERROR at line 1: ORA-00001: unique constraint (DOC92.REFERENCE_IS_UNQIUE) violated
The following example shows how the database will enforce the referential constraint USER_IS_VALID, which states that the value of the element /PurchaseOrder/User, that translates to the SQLAttribute xmldata.user", must match one of the values of ENAME in SCOTT.EMP.
insert into xml_purchaseorder values ( xmltype(getDocument('HACKER-20011127121040988PST.xml')) ); insert into xml_purchaseorder values ( *
This returns:
ERROR at line 1: ORA-02291: integrity constraint (SCOTT.USER_IS_VALID) violated - parent key notfound
XML documents are by nature hierarchical animals. The information they contain is represented by a hierarchy of elements, child elements, and attributes. XML documents also view the world around them as a hierarchy. When an XML document refers to another XML document, or any other kind of document, it does so using a URL. URLs can be either relative or absolute. In either case, the URL defines a path to the target document. The path is expressed in terms of a folder hierarchy.
Oracle XML DB Repository makes it possible to view all of XML content stored in the database using a File / Folder metaphor. The Repository provides support for basic operations such as creating files and folders as well as more advanced features such as version and access control.
The Repository is fully accessible, queryable, and updatable through SQL. It can also be directly accessed through industry standard protocols such as HTTP, WebDAV, and FTP.
WebDAV is an Internet Engineering Task Force (IETF) Standard for Distributed Authoring and Versioning of content. The standard is implemented by extending the HTTP protocol allowing a Web Server to act as a File Server in a distributed environment.
Oracle XML DB Repository is based on the model defined by the WebDAV standard. It uses the WebDAV resource model to define the basic metadata that is maintained for each document stored in the Repository. The WebDAV protocol uses XML to transport metadata between the client and the server.
Hence, you can easily create, edit, and access documents stored in Oracle XML DB Repository using standard tools. For example, you can use:
WebDAV uses the term Resource to define a file or folder. It defines a set of basic operations that can be performed on a Resource. These operations require a WebDAV server to maintain a set of basic metadata for each Resource. Oracle XML DB exposes this metadata as a set of XML Documents in the following form:
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Invalid="false" Container="false" CustomRslv="false"> <CreationDate> 2002-02-14T16:01:01.066324000</CreationDate> <ModificationDate> 2002-02-14T16:01:01.066324000</ModificationDate> <DisplayName>testFile.xml</DisplayName> <Language>us english</Language> <CharacterSet>utf-8</CharacterSet> <ContentType>text/xml</ContentType> <RefCount>1</RefCount> <ACL> <acl description="/sys/acls/all_all_acl.xml" xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd"> <ace> <grant>true</grant> <privilege> <all/> </privilege> <principal>PUBLIC</principal> </ace> </acl> </ACL> <Owner>DOC92</Owner> <Creator>DOC92</Creator> <LastModifier>DOC92</LastModifier> <SchemaElement> http://xmlns.oracle.com/xdb/XDBSchema.xsd#binary </SchemaElement> <Contents> <binary>02C7003802C77B7000081000838B1C240000000002C71E7C</binary> </Contents> </Resource>
Oracle XML DB exposes the Repository to SQL developers as two views:
It also provides a set of SQL functions and PL/SQL packages for performing Repository operations.
RESOURCE_VIEW is the primary way for querying Oracle XML DB Repository. There is one entry in the RESOURCE_VIEW for each document stored in the Repository. The RES column contains the resource entry for the document, the ANY_PATH entry provides a valid folder path from the root to the resource.
The definition of the RESOURCE_VIEW is:
SQL> describe RESOURCE_VIEW Name Null? Type ------------------------------- -------- ---------------------------- RES SYS.XMLTYPE ANY_PATH VARCHAR2(4000)
PATH_VIEW contains an entry for each Path in the Repository. Since a Resource can be linked into more than one folder, PATH_VIEW shows all possible Paths in the Repository and the resources they point to. The definition of the PATH_VIEW is:
SQL> describe PATH_VIEW Name Null? Type ------------------------------- -------- ---------------------------- PATH VARCHAR2(1024) RES SYS.XMLTYPE LINK SYS.XMLTYPE
You can create new folders and documents using methods provided by DBMS_XDB package. For example, a new folder can be created using the procedure createFolder() and a file can be uploaded into that folder using createResource(). The following examples show you how to do this:
SQL> declare 2 result boolean; 3 begin 4 result := dbms_xdb.createFolder('/public/testFolder'); 5 end; 6 / PL/SQL procedure successfully completed. SQL> declare 2 result boolean; 3 begin 4 result := dbms_xdb.createResource( 5 '/public/testFolder/testFile.xml', 6 getDocument('testFile.xml') 7 ); 8 end; 9 / PL/SQL procedure successfully completed.
RESOURCE_VIEW can be queried just like any other view. Oracle XML DB provides a new operator, UNDER_PATH, that provides a way for you to restrict queries to a particular folder tree within the RESOURCE_VIEW.
extractValue() and existsNode() can be used on the Resource documents when querying the RESOURCE_VIEW and PATH_VIEW Resource documents.
You can update Resources using updateXML().
For example, the following query updates the OWNER and NAME of the document created in the previous example.
update RESOURCE_VIEW set RES=updateXML(RES, '/Resource/DisplayName/text()','RenamedFile', '/Resource/Owner/text()','SCOTT' ) where any_path = '/public/testFolder/testFile.xml'; -- 1 row updated. select r.res.getClobVal() from RESOURCE_VIEW r where ANY_PATH = '/public/testFolder/testFile.xml' / -- Results in: -- R.RES.GETCLOBVAL() -- ---------------------------------------------------------------------- -- <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" -- Hidden="false" Invalid="false" Container="false" -- CustomRslv="false"> -- <CreationDate> 2002-02-14T16:01:01.066324000</CreationDate> -- <ModificationDate> 2002-02-14T21:36:39.579663000</ModificationDate> -- <DisplayName>RenamedFile</DisplayName> -- <Language>us english</Language> -- <CharacterSet>utf-8</CharacterSet> -- <ContentType>text/xml</ContentType> -- <RefCount>1</RefCount> -- <ACL> -- ... -- </ACL> -- <Owner>SCOTT</Owner> -- <Creator>DOC92</Creator> -- <LastModifier>DOC92</LastModifier> -- </Resource>
Resource can be deleted using deleteResource(). If the resource is a folder then the folder must be empty before it can be deleted.
The following examples show the use of the deleteResource() procedure.
call dbms_xdb.deleteResource('/public/testFolder') / call dbms_xdb.deleteResource('/public/testFolder') * ERROR at line 1: ORA-31007: Attempted to delete non-empty container /public//testFolder ORA-06512: at "XDB.DBMS_XDB", line 151 ORA-06512: at line 1 call dbms_xdb.deleteResource('/public/testFolder/testFile.xml') / Call completed. call dbms_xdb.deleteResource('/public/testFolder') / Call completed.
RESOURCE_VIEW and PATH_VIEW are based on tables stored in Oracle XML DB database schema. The metadata exposed through RESOURCE_VIEW and PATH_VIEW is stored and managed using a set of tables in Oracle XML DB-supplied XML schema, XDBSchema.xsd. The contents of the files are stored as BLOB or CLOB columns in this XML schema.
| See Also:
Appendix G, "Example Setup scripts. Oracle XML DB - Supplied XML Schemas", "xdbconfig.xsd: XML Schema for Configuring Oracle XML DB" |
There is an exception to this storage paradigm when storing XML schema-based XML documents. When an XML schema is registered with Oracle XML DB you can define a default storage table for each root element defined in the XML schema.
You can define your own default storage tables by adding an xdb:defaultTable attribute to the definition of the top level element When the schema is registered, Oracle XML DB establishes a link between the Repository and the default tables defined by your XML schema. You can choose to generate the default tables as part of the XML schema registration.
A default table is an XMLType table, that is, it is an object table based on the XMLType datatype. When an XML document, with a root element and XML schema that match your default table's root element and XML schema, is inserted into the Repository, the XML content is stored as a row in the specified default table. A resource is created that contains a reference to the appropriate row in the default table.
One of the special features of an XMLType table is that it can be hierarchically enabled. Default Tables, created as part of XML schema registration are automatically hierarchically enabled. When a table is hierarchically enabled DML operations on the default table may cause corresponding operations on the Oracle XML DB Repository. For example, when a row is deleted from the default table, any entries in the Repository which reference that row are deleted.
The following example shows the result of adding an xdb:defaultTable attribute to the XML schema definition's PurchaseOrder element and then registering the XML schema with the Create Table option set to TRUE:
<xs:element name="PurchaseOrder" xdb:defaultTable="XML_PURCHASEORDER"> <xs:complexType type="PurchaseOrderType" xdb:SQLType="XML_PURCHASEORDER_TYPE"> <xs:sequence> <xs:element ref="Reference"/> <xs:element name="Actions" type="ActionsType"/> <xs:element name="Reject" type="RejectType" minOccurs="0"/> <xs:element ref="Requestor"/> <xs:element ref="User"/> <xs:element ref="CostCenter"/> <xs:element name="ShippingInstructions" type="ShippingInstructionsType"/> <xs:element ref="SpecialInstructions" xdb:SQLName="SPECINST"/> <xs:element name="LineItems" type="LineItemsType"/> </xs:sequence> </xs:complexType> </xs:element> SQL> begin 2 dbms_xmlschema.registerSchema( 3 'http://www.oracle.com/xsd/purchaseOrder.xsd', 4 getDocument('purchaseOrder3.xsd'), 5 TRUE, TRUE, FALSE, TRUE 6 ); 7 8 end; 9 / PL/SQL procedure successfully completed. SQL> describe XML_PURCHASEORDER Name Null? Type ------------------------------- -------- ---------------------------- TABLE of SYS.XMLTYPE(XMLSchema http://www.oracle.com/xsd/purchaseOrder.xsd Element "PurchaseOrder") STORAGE Object-relational TYPE "XML_PURCHASEORDER_TYPE"
The following example shows how, once the XML schema is registered, and the default table created, when inserting an XML document into Oracle XML DB Repository causes a row to be inserted into the designated default table:
select count(*) from XML_PURCHASEORDER;
Results in:
COUNT(*) ---------- 0 -- create testFolder declare result boolean; begin result := dbms_xdb.createFolder('/public/testFolder'); end; / declare result boolean; begin result := dbms_xdb.createResource( '/public/testFolder/purchaseOrder1.xml', getDocument('purchaseOrder1.xml') ); end; / -- PL/SQL procedure successfully completed. commit; -- Commit complete. select count(*) from XML_PURCHASEORDER;
Results in:
COUNT(*) ---------- 1
This example shows when deleting a row from the hierarchy-enabled default table, the corresponding entry is deleted from the hierarchy:
select extractValue(res,'Resource/DisplayName') "Filename" from RESOURCE_VIEW where under_path(res,'/public/testFolder') = 1; /
Results in:
Filename ---------------------------------------------------------------------- purchaseOrder1.xml delete from XML_PURCHASEORDER; 1 row deleted. SQL> commit; Commit complete. select extractValue(res,'Resource/DisplayName') "Filename" from RESOURCE_VIEW where under_path(res,'/public/testFolder') = 1 /
Results in:
no rows selected
When a resource describes XML content that has been stored in a default table the resource entry itself simply contains a reference to the appropriate row in the default table. This reference can be used to perform join operations between the resource and it's content. This can be seen in the following example.
XDBUriType can be used to access the contents of a file stored in the Repository using a logical path. The following example shows how to access a resource associated with a JPEG file. The JPEG file has been inserted into the Repository. The example uses Oracle interMedia ordsys.ordimage class to extract the metadata associated with the JPEG file.
create or replace function getImageMetaData (uri varchar2) return xmltype deterministic is resType xmltype; resObject xdb.xdb$resource_t; attributes CLOB; xmlAttributes xmltype; begin DBMS_LOB.CREATETEMPORARY(attributes, FALSE, DBMS_LOB.CALL); -- ordsys.ordimage.getProperties(xdburitype(uri).getBlob(), -- attributes); select res into resType from resource_view where any_path = uri; resType.toObject(resObject); ordsys.ordimage.getProperties(resObject.XMLLOB,attributes); xmlAttributes := xmltype(attributes); DBMS_LOB.FREETEMPORARY(attributes); return xmlAttributes; end; /
Oracle XML DB includes three protocol servers through which you can access the Repository directly from standard file-based applications.
The FTP Protocol Server allows standard FTP clients to access content stored in the Repository as if it were content behind a regular FTP server. FTP Protocol Server works with standard FTP clients, including:
Figure 3-6, Figure 3-7, Figure 3-8, and Figure 3-9 show examples of how you can access the root level of the Repository using various of standard FTP clients.
Oracle XML DB Repository can also be accessed using HTTP and WebDAV. WebDAV support allows applications such as a Microsoft's Web Folders client, Microsoft Office, and Macromedia's Dreamweaver to directly access Oracle XML DB Repository. Figure 3-10 and Figure 3-11 are examples of using HTTP and WebDAV to access the Repository.
By providing support for standard industry protocols, Oracle XML DB makes it possible to upload and access data and documents stored in Oracle9i database using standard, familiar interfaces.
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|