Home

Breaking up XML into Relational Data

Author : Exforsys Inc. Published on: 4th May 2008

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.

Ads

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:

Sample Code

CONN xmlusr/xmlusr
SELECT emps.empno,emps.ename, emps.salary FROM employees,
XMLTable(
'for $i in /EMPLOYEE
return $i'
PASSING OBJECT_VALUE
COLUMNS empno NUMBER PATH '/EMPLOYEE/@id',
ename VARCHAR2(30) PATH '/EMPLOYEE/ENAME',
salary NUMBER PATH '/EMPLOYEE/SALARY') emps

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.

Ads

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.

Read Next: PHP Oracle Web Development

This tutorial is part of a PHP Oracle Training Tutorials tutorial series. Read it from the beginning and learn yourself.

PHP Oracle Training Tutorials