XML-Enabled Applications - Part 3

[next]

XML-Enabled Applications - Part 3

By Yuli Vasiliev

Digg This Add to del.icio.us

[This is an excerpt from the book, PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax, by Yuli Vasiliev. Published by Packt Publishing Ltd., 2007

Retrieving XML Data

To 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$ ------------------------ <EMPLOYEE id="100"> <ENAME>King</ENAME> <SALARY>24000</SALARY> </EMPLOYEE>

The QueryXML.php script defined below shows how the above query might be issued from PHP.

<?php //File: QueryXML.php if(!$rsConnection = oci_connect('xmlusr', 'xmlusr', '//localhost/orcl')) { $err = oci_error(); trigger_error('Could not establish a connection: '.$err['message'], E_USER_ERROR); }; $xpath_exp = '/EMPLOYEE/@id="100"'; $query = 'SELECT value(x).GetStringVal() as RESULT FROM employees x WHERE existsNode(value(x), :xpath) = 1'; $stmt = oci_parse($rsConnection,$query); oci_bind_by_name($stmt, ":xpath", $xpath_exp); if (!oci_execute($stmt)) { $err = oci_error($stmt); trigger_error('Query failed: '.$err['message'], E_USER_ERROR); } $xmlDoc = oci_fetch_assoc($stmt); $dom = new DOMDocument(); $dom->loadXML($xmlDoc['RESULT']); print $dom->saveXML(); ?>

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.phpscript shown in the listing, it should produce the following output:

<?xml version="1.0" ?> <EMPLOYEE id="100"> <ENAME>King</ENAME> <SALARY>24000</SALARY> </EMPLOYEE>

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 ENAMEnode under the EMPLOYEEnode whose idattribute 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 employeestable.

When issued from SQL*Plus, the above query should return the following result:

ENAME -------------------------------------------- King

You might rewrite the query to use the extractand 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:

<?php //File: extractXML.php if(!$rsConnection = oci_connect('xmlusr', 'xmlusr', '//localhost/orcl')) { $err = oci_error(); trigger_error('Could not establish a connection: '.$err['message'], E_USER_ERROR); }; $id = 100; $exist_exp = '/EMPLOYEE/@id='.$id; $extr_exp = '/EMPLOYEE/ENAME/text()'; $query = 'SELECT x.OBJECT_VALUE.extract(:extr).getStringVal() ENAME FROM employees x WHERE x.OBJECT_VALUE.existsNode(:exist)=1'; $stmt = oci_parse($rsConnection,$query); oci_bind_by_name($stmt, ":extr", $extr_exp); oci_bind_by_name($stmt, ":exist", $exist_exp); if (!oci_execute($stmt)) { $err = oci_error($stmt); trigger_error('Query failed: '.$err['message'], E_USER_ERROR); } $xmlDoc = oci_fetch_assoc($stmt); print '<h2>The name of employee whose id='.$id.' is:</h2>'; print $xmlDoc['ENAME']; ?>

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.

[next]

URL: