Tutorials PHP OracleAnalyzing 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:
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:
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:
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:
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:
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.
