alt
Advertisement
Sponsored links
Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow PHP Oracle arrow Retrieving XML Data
Site Search


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.

  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.    ?> 
  

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:

  1. <?php
  2.    //File: extractXML.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.    };
  10.    $id = 100;
  11.    $exist_exp = '/EMPLOYEE/@id='.$id;
  12.    $extr_exp = '/EMPLOYEE/ENAME/text()';
  13.    $query = 'SELECT x.OBJECT_VALUE.extract(:extr).getStringVal() ENAME
  14.       FROM employees x
  15.       WHERE x.OBJECT_VALUE.existsNode(:exist)=1';
  16.    $stmt = oci_parse($rsConnection,$query);
  17.    oci_bind_by_name($stmt, ":extr", $extr_exp);
  18.    oci_bind_by_name($stmt, ":exist", $exist_exp);
  19.    if (!oci_execute($stmt)) {
  20.       $err = oci_error($stmt);
  21.       trigger_error('Query failed: '.$err['message'], E_USER_ERROR);
  22.  
  23.    }
  24.    $xmlDoc = oci_fetch_assoc($stmt);
  25.    print '<h2>The name of employee whose id='.$id.' is:</h2>';
  26.    print $xmlDoc['ENAME'];
  27. ?> 
  

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.

Tags

See All Tags

Trackback(0)
Comments (0)add comment

Write comment

busy
 
< Prev   Next >
[ Back ]
Exforsys Offers
© 2008 Exforsys.com
Joomla! is Free Software released under the GNU/GPL License.
 Page copy protected against web site content infringement by Copyscape