| home / programming / programming/php / xml-enabled-applications3 |
|
|
|
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:
This query should produce the following output:
The QueryXML.php script defined 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.phpscript shown in the listing, it should produce the following output:
If your browser omits XML tags, though, you will see the following:
Digg This
Add to del.icio.us
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:
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:
You might rewrite the query to use the extractand existsNode XMLType methods as follows:
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.
| home / programming / programming/php / xml-enabled-applications3 |
| ||||||||||||||||||||
URL:
