XML-Enabled Applications - Part 2 | Page 2

[previous] [next]

XML-Enabled Applications - Part 2

Using XMLType for Handling XML Data in the Database

Being an object type, XMLType can not only be used to store XML data in the database but also to operate on that data via its built-in methods. Regardless of the storage model you choose, XMLType provides a set of XML-specific methods to operate on XMLType instances.

The most commonly used methods of XMLType are listed in the following table:

You saw an example of using an XMLType method in the preceding sample application. In particular, in the DBServerXSLTrans.php script discussed in the Performing XSLT Transformations inside the Database section you use the getStringVal method of XMLType to retrieve the generated XHTML data as a VARCHAR value, so that it can then be loaded in a DOMDocument instance. If you recall, the query used in the DBServerXSLTrans.php script looks as follows:

$query = 'SELECT XMLtransform(x.xmlcol, (SELECT stylesheet FROM XSLTstylesheets WHERE id = 1)).getStringVal() AS result FROM (SELECT XMLELEMENT("EMPLOYEES", XMLAgg( XMLELEMENT("EMPLOYEE", XMLATTRIBUTES(employee_id AS "id"), XMLFOREST(last_name AS "ENAME", salary AS "SALARY") ) ) ) AS xmlcol FROM hr.employees WHERE department_id=:deptid) x';

To see another XMLType method in action, namely transform, you might rewrite the above SQL statement as follows:

$query = 'SELECT x.xmlcol.transform((SELECT stylesheet FROM XSLTstylesheets WHERE id = 1)).getStringVal() AS result FROM (SELECT XMLELEMENT("EMPLOYEES", XMLAgg( XMLELEMENT("EMPLOYEE", XMLATTRIBUTES(employee_id AS "id"), XMLFOREST(last_name AS "ENAME", salary AS "SALARY") ) ) ) AS xmlcol FROM hr.employees WHERE department_id=:deptid) x';

In the above query, you use the transformXMLType method as an alternative to the XMLtransformSQL function used in the original query. Since both transform and XMLtransform have the same functionality, the above queries will produce the same general result.

[ XMLtransform is not the only example of an SQL function providing the same functionality as the appropriate XMLType method. In fact, Oracle XML DB provides analogous SQL functions for many XMLType methods. For example, XMLType methods extract and existsNode can be used instead of the SQL functions having the same names. ]

As you can see, the above queries operate on relational data, and transform it into XML format with SQL/XML generation functions. Before looking at the approaches you can take to retrieve XML data stored in the database natively, however, it would be a good idea to look at how you can create an XMLType storage in Oracle XML DB. The following section discusses how you can do this with the help of the XML Schema feature.

[previous] [next]

URL: