Exforsys.com
 
Home Tutorials PHP Oracle
 

Retrieving XML Data

 

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$ ------------------------ King 24000

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


Sample Code
  1.         <?php
  2.                 //File: QueryXML.php
  3.                 if(!$rsConnection = oci_connect('xmlusr', 'xmlusr',
  4.                 '//localhost/orcl')) {
  5.                      $err = oci_error();
  6.                      trigger_error('Could not establish a connection:
  7.                      '.$err['message'], E_USER_ERROR);
  8.                 };
  9.                 $xpath_exp = '/EMPLOYEE/@id="100"';
  10.                 $query = 'SELECT value(x).GetStringVal() as RESULT
  11.                      FROM employees x
  12.                      WHERE existsNode(value(x), :xpath) = 1';
  13.                 $stmt = oci_parse($rsConnection,$query);
  14.                 oci_bind_by_name($stmt, ":xpath", $xpath_exp);
  15.                 if (!oci_execute($stmt)) {
  16.                      $err = oci_error($stmt);
  17.                      trigger_error('Query failed: '.$err['message'], E_USER_ERROR);
  18.                 }
  19.                 $xmlDoc = oci_fetch_assoc($stmt);
  20.                 $dom = new DOMDocument();
  21.                 $dom->loadXML($xmlDoc['RESULT']);
  22.                 print $dom->saveXML();
  23.         ?>
Copyright exforsys.com


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:


Sample Code
  1. <?php
  2.         //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);      
  3.         };      $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);
  4.         }       $xmlDoc = oci_fetch_assoc($stmt);       print '<h2>The name of employee whose id='.$id.' is:</h2>';     print $xmlDoc['ENAME'];?>
Copyright exforsys.com


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.


Accessing Relational Data Through XMLType Views

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.



Read Next: Using XMLType Views



 

 

Comments



Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2010 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape