alt
Advertisement
Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow PHP Oracle arrow Performing DML Operations on XML Schema Based XMLType Views
Site Search


Performing DML Operations on XML Schema Based XMLType Views

Performing DML Operations on XML Schema-Based XMLType Views

Analyzing the underlying query of the empSch_v view discussed in the preceding section, you may note that each attribute of the EMP_T object used in the select list maps to a certain column of a single table, namely emps. What this means in practice is that the empSch_v view can be inherently updated, so you can perform DML operations against it without having to write INSTEAD-OF triggers.

The following figure gives a conceptual depiction of what occurs upon insertion of an XML document into an inherently updatable XML schema-based XMLType view.

Here is the explanation of the steps outlined in the previous figure:

  • Step 1: PHP script posts a schema-based XML document to be inserted into an XML schema-based XMLType view.
  • Step 2: Oracle checks whether the XML document being inserted into the view conforms to the XML schema on which the view is defined.
  • Step 3: If the document conforms to the schema, it is shredded into relational data conforming to the underlying relational table.
  • Step 4: The shredded XML document is inserted into the underlying relational table, as a new row.

Turning back to the empSch_v view, you might issue the following INSERT statement against it from SQL*Plus in order to make sure that the view actually allows you to perform INSERT operations on it:

  1. INSERT INTO empSch_v VALUES(XMLType(
  2.      '<employee id="300" 
  3.           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  4.           xsi:nonamespaceschemalocation="emp.xsd"></employee>
  5.      <ename></ename>Silver
  6.      <salary></salary>12000
  7.      ')
  8. );
  9.  
  10. COMMIT; 
  

NOTE: Issuing a statement from SQL*Plus is always a good idea when you need to perform a quick test. All INSERT operations discussed in this section might be issued from within PHP code as well.

Note the use of the xsi:noNamespaceSchemaLocation attribute of the root document element EMPLOYEE in the above statement. This attribute is used to indicate the schema location. Alternatively, you might use the createSchemaBasedXML method of XMLType, as you did in the Using XML Schemas section when inserting a row into the employees table. However, in this example you would specify emp.xsd as the parameter of createSchemaBasedXML.

The data inserted through the empSch_v view can then be accessed not only through that view as XML, but also through its underlying table emps as relational data. For example, to retrieve the employee XML document inserted into the empSch_v view by the preceding query, you might use the following query:

SELECT * FROM empSch_v WHERE existsNode(OBJECT_VALUE,
	'/EMPLOYEE/@id="300"')=1;

On the other hand, to see a relational representation of the inserted document, you might issue the following query against the emps underlying table:

SELECT * FROM emps WHERE employee_id=300;

This should produce the following output:

EMPLOYEE_ID LAST_NAME                 SALARY
----------- ------------------------- ----------
300         Silver 	              12000

Now, what happens if you try to insert an employee XML document into empSch_v, which doesn't conform the emp.xsd XML schema? Say, for example, the value of the SALARY element in the inserted document exceeds the maximum allowable value specifi ed for this element in the schema. For example, you might issue the following statement and see what happens:

  1. INSERT INTO empSch_v VALUES(XMLType(
  2.    '<employee id="301" 
  3.              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  4.              xsi:nonamespaceschemalocation="emp.xsd"></employee>
  5.    <ename></ename>Jamison
  6.    <salary></salary>100000
  7.    ')
  8. ); 
  

You might be surprised to see that the above statement works without any problem. This is despite the fact that the value of the SALARY element is restricted to be less than 100000, as you might recall from the listing in the Creating XML Schema-Based XMLType Views section, describing emp.xsd XML schema registration.

The fact is that Oracle performs only a partial validation when it comes to inserting an XML document into an XML schema-based XMLType table or column or view. In particular, it checks to see whether the structure of the XML document being inserted conforms to the appropriate XML schema and does not check the contents of the document.

So, to ensure that the employee XML documents inserted into the empSch_v view are fully compliant with the emp.xsd XML schema, you need to explicitly invoke an XML schema validation when performing INSERT operations. The simplest way to do this is to use a PL/SQL function that might be created as follows:

  1. CONN xmlusr/xmlusr
  2.    CREATE OR REPLACE FUNCTION val_xml (xmldoc XMLType)
  3.    RETURN XMLType
  4.    IS
  5.       tmpxml XMLType;
  6.       BEGIN
  7.       tmpxml := xmldoc;
  8.       XMLTYPE.schemavalidate(tmpxml);
  9.       RETURN xmldoc;
  10.    END;
  11. / 
  

NOTE: Issuing a statement from SQL*Plus is always a good idea when you need to perform a quick test. All INSERT operations discussed in this section might be issued from within PHP code as well.

After you have created the val_xml function, you might use it in INSERT operations issued against XML schema-based tables and views as follows:

  1. INSERT INTO empSch_v VALUES(VAL_XML(XMLType(
  2.    '<employee id="302" 
  3.             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  4.             xsi:nonamespaceschemalocation="emp.xsd"></employee>
  5.       <ename></ename>Johnson
  6.    <salary></salary>100000
  7.    ')
  8.    )
  9. ); 
  

Now, a full XML schema validation takes place. Since the value of the SALARY element in the above employee XML document is greater than the maximum allowable value defi ned in the schema, you should receive the following error message:

ERROR at line 1:
ORA-31154: invalid XML document
ORA-19202: Error occurred in XML processing
LSX-00292: value "100000" is greater than maximum "100000" (exclusive)
ORA-06512: at "SYS.XMLTYPE", line 345
ORA-06512: at "XMLUSR.VAL_XML", line 7

However, it is important to note that while a full XML schema validation allows you to validate both the structure and contents of an instance document, it comes at the cost of processing time and memory usage, thus adding overhead to your application and decreasing performance.

Tags

See All Tags

Trackback(0)
Comments (0)add comment

Write comment

busy
 
< Prev   Next >
[ Back ]
Exforsys Offers
© 2008 Exforsys.com
Joomla! is Free Software released under the GNU/GPL License.
 Page copy protected against web site content infringement by Copyscape