XML-Enabled Applications - Part 3 | Page 2
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.
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:
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:
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:
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.
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
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:
This should return the following result:
Since DOM fidelity is not required when it comes to wrapping relational data in XML, you set the attribute
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
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
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.
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
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
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.