Tutorials PHP OracleTo retrieve XML data from an XMLType table, you can use a SELECT SQL statement, just as you would if you had to query a relational table. For example, to select the employee with the id set to 100 from the employees XMLType table discussed in the preceding section, you might issue the following query from SQL*Plus when connected as xmlusr/xmlusr:
SELECT * FROM employees x WHERE existsNode(value(x), '/EMPLOYEE/@id="100"') = 1;
This query should produce the following output:
SYS_NC_ROWINFO$
------------------------
The QueryXML.php script defi ned below shows how the above query might be issued from PHP.
In the above script, you set the $xpath_exp variable to the XPath expression that points to the EMPLOYEE node whose id attribute is set to 100. This variable is then bound to the :xpath placeholder.
Note the use of the value(x) pseudocolumn in the select list of the query. In this example, value(x) is used to access the XMLType object representing an employee XML document retrieved by the query. You use the GetStringVal XMLType method to convert the retrieved XML document into a string, so that it can be loaded into a DOMDocument.
When you run the QueryXML.php script shown in the listing, it should produce the following output:
King
24000
If your browser omits XML tags, though, you will see the following:
King 2400
While the existsNode SQL function used in the preceding example checks for the existence of elements based on the XPath expression, the extractValue SQL function lets you extract the value of a node or attribute conforming to the specified XPath expression. So, the extractValue SQL function lets you access XML data, receiving results similar to those received when querying relational data.
The following figure illustrates this point diagrammatically.
The following query is a simple example of extractValue in action:
SELECT extractValue(OBJECT_VALUE, '/EMPLOYEE/ENAME')
ENAME FROM employees WHERE existsNode(OBJECT_VALUE,
'/EMPLOYEE/@id="100"') = 1;
As you can see, the query extracts the value of the ENAME node under the EMPLOYEE node whose id attribute is set to 100. Note the use of the OBJECT_VALUE pseudocolumn in the query. This pseudocolumn is an Oracle Database 10g alternative to value(x). In this query, you use OBJECT_VALUE to access an employee XMLType object retrieved from the employees table. When issued from SQL*Plus, the above query should return the following result:
ENAME -------------------------------------------- King
You might rewrite the query to use the extract and existsNode XMLType methods as follows:
SELECT x.OBJECT_VALUE.extract('/EMPLOYEE/ENAME/text()').getStringVal()
ENAME FROM employees x
WHERE x.OBJECT_VALUE.existsNode('/EMPLOYEE/@id="100"')=1;
To test this query with PHP, you might write the extractXML.php script shown below:
The query used in the script represents a simple example of using the extractValue SQL function. Usually, extractValue is used in complex SQL statements in which the data extracted from XML is then used in INSERT or UPDATE operations performed on relational tables.
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 fi ne-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 rowlevel 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.
