XML-Enabled Applications - Part 2 | Page 3

[previous]

XML-Enabled Applications - Part 2

Using XML Schemas

The simplest way to create an XMLType storage structure in Oracle XML DB is by registering an appropriate XML schema against the database. As a part of the registration process, Oracle automatically creates the storage for a particular set of XML documents, based on the information provided by the schema.

[ An XML schema can be thought of as the metadata describing a certain class of XML documents. So, an XML document conforming to a particular XML schema can be considered as an instance of this XML schema. ]

You might want to use an XML schema for:

Building the storage for XML documents conforming the schema Setting up business rules on XML content of conforming documents Validating XML documents conforming to the schema

However, before you can use an XML schema, you have to create and then register it against the database. Both these tasks can be accomplished in one step with the registerschema procedure from the DBMS_XMLSCHEMAPL/SQL package. For example, to register an XML schema to which the following employee XML document conforms:

<EMPLOYEE id="100"> <ENAME>King</ENAME> <SALARY>24000</SALARY> </EMPLOYEE>

You might issue the following statements:

CONN /as sysdba GRANT ALTER SESSION TO xmlusr; CONN xmlusr/xmlusr BEGIN DBMS_XMLSCHEMA.registerschema( 'employee.xsd', '<xs:schema xmlns:xs="https://www.w3.org/2001/XMLSchema" xmlns:xdb="https://xmlns.oracle.com/xdb"> <xs:element name="EMPLOYEE" type="EMPLOYEE_TYP" xdb:defaultTable="EMPLOYEES" xdb:columnProps= "CONSTRAINT emp_pkey PRIMARY KEY (XMLDATA.empno)"/> <xs:complexType name="EMPLOYEE_TYP" xdb:SQLType="EMPLOYEE_T"> <xs:sequence> <xs:element name="ENAME" type="xs:string" xdb:SQLName="ENAME" xdb:SQLType="VARCHAR2"/> <xs:element name="SALARY" type="xs:double" xdb:SQLName="SALARY" xdb:SQLType="NUMBER"/> </xs:sequence> <xs:attribute name="id" type="xs:positiveInteger" xdb:SQLName="EMPNO" xdb:SQLType="NUMBER"/> </xs:complexType> </xs:schema>', TRUE, TRUE, FALSE, TRUE ); END; /

As you can see, the DBMS_XMLSCHEMA.registerschema procedure takes two arguments. The first one is the string representing the name under which you want to register the schema against the database, and the other one is the document containing the schema itself.

In this example, the root element of the XML schema includes two namespace declarations, namely the XML schema namespace declaration and Oracle XML DB namespace declaration. To denote these namespaces, you use prefixes: xs and xdb respectively.

By including the XML schema namespace declaration, you obtain the ability to use the elements and attributes defined in this namespace, as well as the data types defined by the XML Schema language. For example, in the above example you specify the positiveInteger XML Schema language data type for the id attribute of the EMPLOYEE element.

The Oracle XML DB namespace lets you use annotations in the schema. For example, you use the xdb:defaultTable annotation to tell Oracle to use the specified table name when generating an XMLType table that will be used for storing XML documents conforming to the schema, rather than using a system-generated name for that table. In this particular example, you specify EMPLOYEES as the name for this table.

Another interesting annotation used in this XML schema is the xdb:columnProps. In this example, you use this annotation to define a primary key on the EMPLOYEE element's id attribute mapped to the EMPNO attribute of the EMPLOYEE_T SQL object type.

By including the xdb:SQLName annotation you make sure that the name of the generated SQL object type will be EMPLOYEE_T.

Finally, note the use of the flags passed to the DBMS_XMLSCHEMA.registerschema procedure:

TRUE, TRUE, FALSE, TRUE

The above flags indicate the following (in the same order as they appear in the listing):

The schema is generated as local (visible only to the database user who created it) Appropriate SQL object types are generated Java beans are not generated Default tables are generated

After registering the schema, you might want to look at the database object generated during the registration. The following listing contains the SQL statements that you might issue from SQL*Plus to make sure that Oracle generated the objects annotated in the schema. For convenience, the listing also contains the output produced.

DESC employee_t employee_t is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T EMPNO NUMBER(38) ENAME VARCHAR2(4000 CHAR) SALARY NUMBER DESC employees Name Null? Type ----------------------------------------- -------- ----------------- TABLE of SYS.XMLTYPE( XMLSchema "employee.xsd" Element "EMPLOYEE") STORAGE Object-relational TYPE "EMPLOYEE_T"

As you can see, Oracle generated the employee_t object type and employees XMLType table based on this object type, as a part of the XML schema registration process. Note that the names of the generated objects have been defined in the schema. If you recall, you set the value of the xdb:SQLName attribute of global element EMPLOYEE to EMPLOYEE_T, and the xdb:defaultTable attribute to EMPLOYEES.

[ It's interesting to note that the names of database objects generated during the XML schema registration process are case sensitive. However, since SQL is case insensitive, you can refer to these objects in SQL disregarding the case of their names. The names of XML elements and attributes specified in an XML schema are also case sensitive. However, unlike SQL, XML is case-sensitive, which means you must refer to XML elements and attributes in XML code using the case with which they were defined in the XML schema. ]

Now that you have defined the XMLType storage for employee XML documents, you might want to load some data into the employees XML schema-based XMLType table generated during the schema registration. The simplest way to do this is to use the INSERT SQL statement, as follows:

CONN xmlusr/xmlusr INSERT INTO employees VALUES( XMLType( '<EMPLOYEE id="100"> <ENAME>King</ENAME> <SALARY>24000</SALARY> </EMPLOYEE>' ).createSchemaBasedXML('employee.xsd') ); COMMIT;

In the above example, you use the createSchemaBasedXML method of XMLType to explicitly identify the employee XML schema when inserting a new row into the employees table.

Now, if you try to issue the INSERT statement shown in the listing again, you will receive the following error message:

ERROR at line 1: ORA-00001: unique constraint (XMLUSR.EMP_PKEY) violated

As you can see, an attempt to insert the same row into the employeestable fails due to a EMP_PKEY primary key constraint violation. If you recall, you define the EMP_ PKEY primary key on the idattribute of the EMPLOYEE element in the XML schema registered as discussed in this section earlier. This constraint makes it impossible to insert two employee XML documents with the same ID into the employees table.

[ Another way to load data into the employees table is via one of the internet protocols supported by Oracle XML DB. This mechanism is discussed in the Taking Advantage of Standard Internet Protocols section later in this chapter. ]

Finally, it's worth noting that you can always delete a registered XML schema along with all the database objects generated during its registration. For example, to delete the employee XML schema registered as discussed earlier in this section, you might issue the following PL/SQL block:

CONN xmlusr/xmlusr BEGIN DBMS_XMLSCHEMA.deleteSchema( SCHEMAURL => 'employee.xsd', DELETE_OPTION => dbms_xmlschema.DELETE_CASCADE_FORCE); END; /

[ Since the employees table is used in the subsequent examples, make sure to register the employee XML schema again as discussed earlier in this section. Also make sure to insert a row into the table as shown earlier in this section. ]

Besides deleting the employee XML schema, the above code deletes the employee_t object type and employees XMLType table generated during the schema registration process.

[ For more information on using XML schemas in Oracle XML DB, you can refer to Oracle documentation: chapters: XML Schema Storage and Query: Basic and XML Schema Storage and Query: Advanced in the Oracle XML DB Developer's Guide. Also, for information on XML Schema language, you can refer to the W3C XML Schema Recommendation. ]

[This is an escerpt from the book, PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax, by Yuli Vasiliev. Published by Packt Publishing Ltd., 2007

Digg This Add to del.icio.us

[previous]

URL: