Breaking up XML into Relational Data

Breaking up XML into Relational Data

While the preceding example shows how to construct an XML representation over relational data, the example in this section illustrates how you can shred XML data back into relational data. This reverse operation can be useful if your application works with relational data rather than XML, but the data which you work with, is stored in XML format.

Turning back to the employees XMLType table generated during the registration of the employee.xsd XML schema discussed in the Using XML Schemas section, you might use the SQL function XMLTable to shred the employee XML documents into individual columns of a virtual table, as shown below:

Assuming that you have followed the instructions in the preceding sections, the output generated by the query shown in the listing might look like this:

EMPNO ENAME SALARY
----- ------------------------------ ----------
100 King 24000
101 Horka 25000
303 Locke 7000
304 Polonski 8200

In a real-world situation, in order to hide data complexity, you might find it useful to build a view on the query shown in the listing, so that the people designing SQL queries against that view have no idea they are dealing with data actually stored in XML format.

Summary

When building XML-enabled PHP/Oracle applications, the database can not only be used as an efficient means for storing XML data, but also to operate on any kind of data that can be expressed in XML.

In this chapter you learned how to use XML techniques and technologies available in PHP and Oracle when building XML-enabled PHP/Oracle applications. In particular, you saw how to use PHP's XML extensions and how to take advantage of Oracle XML DB, a set of Oracle XML technologies making Oracle Database an ideal choice for data-driven XML applications.

By now you should have a good understanding of how you can use XML techniques and technologies available in PHP and Oracle to build robust XML-enabled PHP applications on Oracle Database. Armed with this knowledge, you will be able to understand better XML Web Services discussed in the next chapter.