XML-Enabled Applications - Part 3 | Page 2

[previous] [next]

XML-Enabled Applications - Part 3

Accessing Relational Data Through XMLType Views

Using relational tables to store shredded XML documents allows you to take advantage of both the Oracle XML technologies and Oracle database relational technologies when developing XML-enabled applications.

[ For example, you can easily implement fine-grained access when working with XML content built upon relational data. In Chapter 9 Web Services, you will see an example of how to secure XML data, based on the row-level security implemented on the relational data upon which that XML data is built. ]

In the preceding sections, you saw several examples of how to construct XML from SQL data with the help of SQL/XML generation functions. In the following sections, you will learn how to simplify the development of XML-enabled PHP/Oracle applications with XMLType views built upon relational tables.

Using XMLType Views

XMLType views provide a convenient way to construct XML representations of relational data without physically migrating that data into XML. Once written, an XMLType view may be used in various queries, making them simpler and so increasing their readability.

Turning back to the SELECT statement used in the SQLXMLQuery.php script discussed in the Using Oracle SQL/XML Generation Functions section earlier in this chapter, you might create an XMLType view based on that statement as shown below.

CONN /as sysdba GRANT CREATE ANY VIEW TO xmlusr; CONN xmlusr/xmlusr; CREATE VIEW EmpsXML AS SELECT XMLELEMENT("EMPLOYEES", XMLAgg( XMLELEMENT("EMPLOYEE", XMLATTRIBUTES(employee_id AS "id"), XMLFOREST(last_name AS "ENAME", salary AS "SALARY") ) ) ) AS xmlcol, department_id AS dept_id FROM hr.employees GROUP BY department_id;

In this example, you start by granting the CREATEVIEW privilege to the xmlusr database schema and then, when connected as xmlusr/xmlusr, create the EmpsXML view based on the query that uses SQL/XML functions to generate XML from the data stored in the hr.employeesrelational table.

The good thing about the EmpsXML view is that it hides the details of generating an employees XML document, thus letting you write simpler and more readable queries. With it, the query used in the SQLXMLQuery.php script might be rewritten as follows:

$query = 'SELECT xmlcol as RESULT FROM EmpsXML WHERE dept_id=:deptid';

Before running the updated SQLXMLQuery.php script, make sure to specify the xmlusr/xmlusr schema in the oci_connect function at the beginning of the script as follows:

$rsConnection = oci_connect('xmlusr', 'xmlusr', '//localhost/orcl'))

Also, you might rewrite the query string used in the DBServerXSLTrans.php script discussed in the Performing XSLT Transformations inside the Database section earlier in this chapter as follows:

$query = 'SELECT XMLtransform(x.xmlcol, (SELECT stylesheet FROM XSLTstylesheets WHERE id = 1)).getStringVal() AS result FROM (SELECT * FROM EmpsXML WHERE dept_id=:deptid) x';

As you can see, the above query is three times smaller than the one originally used in the DBServerXSLTrans.php script.

Creating XML Schema-Based XMLType Views

While the Using XML Schemas section earlier in this chapter focuses on how the XML Schema feature of Oracle XML DB can be used to create an XML schema-based storage structure, this section discusses how XML schema functionality might be used when working with existing relational data, without having to change the physical structure of that data.

[ Creating an XML schema-based XMLType view is the most common way to take advantage of XML schema functionality when dealing with data stored relationally. ]

However, before you create an XML schema-based XMLType view, you must have the appropriate XML schema created and registered against the database. By executing the statement shown overleaf, you create and register the emp.xsd XML schema on which you will then create an XMLType view.

CONN xmlusr/xmlusr BEGIN DBMS_XMLSCHEMA.registerschema( 'emp.xsd', '<xs:schema xmlns:xs="https://www.w3.org/2001/XMLSchema" xmlns:xdb="https://xmlns.oracle.com/xdb"> <xs:element name="EMPLOYEE" type="EMP_TYP"/> <xs:complexType name="EMP_TYP" xdb:SQLType="EMP_T" xdb:maintainDOM="false"> <xs:sequence> <xs:element name="ENAME" type ="enameType" xdb:SQLName="ENAME" xdb:SQLType="VARCHAR2"/> <xs:element name="SALARY" type ="salaryType" xdb:SQLName="SALARY" xdb:SQLType="NUMBER"/> </xs:sequence> <xs:attribute name="id" type="xs:positiveInteger" xdb:SQLName="EMPNO" xdb:SQLType="NUMBER"/> </xs:complexType> <xs:simpleType name="salaryType"> <xs:restriction base="xs:double"> <xs:maxExclusive value="100000"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="enameType"> <xs:restriction base="xs:string"> <xs:minLength value="2"/> <xs:maxLength value="30"/> </xs:restriction> </xs:simpleType> </xs:schema>', TRUE, TRUE, FALSE, FALSE ); END; /

As you can see from the listing, the EMPLOYEE element, which is the root element of the employee XML document described by this schema, is mapped to the EMP_T SQL object type. This object type will be automatically generated during schema registration as long as you set the DBMS_XMLSCHEMA.registerschema's fourth parameter, which is actually called GENTYPES, to TRUE.

At the same time, you set the sixth (GENTABLES) parameter to FALSE, thus instructing Oracle not to create any tables during schema registration. This makes sense in this case because you are going to map between this XML schema and an existing relational table later, with the help of an XMLType view.

After the PL/SQL block shown in the listing has been successfully executed, you might issue the DESC SQL command in order to make sure that the EMP_T object type was generated:

DESC EMP_T

This should return the following result:

EMP_T is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------- EMPNO NUMBER(38) ENAME VARCHAR2(4000 CHAR) SALARY NUMBER

Since DOM fidelity is not required when it comes to wrapping relational data in XML, you set the attribute maintainDOM to FALSE. As a result, the EMP_T type, unlike the EMPLOYEE_T type created as discussed in the Using XML Schemas section earlier, doesn't contain the SYS_XDBPD$ attribute.

The XML schema defined in the listing contains an example of how to add a constraint to an element described in the schema, restricting its content to values matching a set of conditions. In particular, you restrict the value of node SALARY in all employee XML documents conforming to the schema to be less than 100 000. To achieve this, you use a maxExclusive element under the restriction element defined in turn under the simpleType element for the SALARY element.

The following listing shows how to set up an XML schema-based XMLType view based on the hr.employees relational table. The view created here conforms to the employee XML schema created as discussed at the beginning of this section.

CONN xmlusr/xmlusr CREATE TABLE emps AS SELECT employee_id, last_name, salary FROM hr.employees; ALTER TABLE emps ADD constraint EMP_PRIMARYKEY PRIMARY KEY (employee_id); CREATE OR REPLACE VIEW empSch_v OF XMLType XMLSCHEMA "emp.xsd" ELEMENT "EMPLOYEE" WITH OBJECT ID (extract(OBJECT_VALUE, '/EMPLOYEE/@id/text()'). getNumberVal()) AS SELECT EMP_T(e.employee_id, e.last_name, e.salary) FROM emps e;

In the above listing, you start by creating relational table emps based on the hr.employees table. For simplicity, you include only three columns in the newly created table, while loading all the rows from hr.employees.

By specifying employee.xsd in the XMLSCHEMA clause and EMPLOYEE in the ELEMENT clause of the CREATEVIEW statement, you constrain a resultant row object in the view to be an instance of the element EMPLOYEE defined in the emp.xsdXML schema.

Since row objects in the empSch_v XMLType object view are synthesized from relational data, you must explicitly choose a set of unique identifiers to be used as object identifiers. In this example, in the WITH clause you specify the id attribute of the EMPLOYEE element as the object identifier because it is unique within the view row objects.

In the select list of the view, you explicitly convert the data retrieved from the relational table emps to the EMP_T SQL object type specified for the EMPLOYEE element in the emp.xsd XML schema.

[previous] [next]

URL: