Exforsys.com
 
Home Tutorials PHP Oracle
 

Using XMLType Views

 

Using XMLType Views

Page 1 of 2

Using XMLType Views

Using XMLType Views XMLType views provide a convenient way to construct XML representations of relational data without physically migrating that data into XML. Once written, an XMLType view may be used in various queries, making them simpler and so increasing their readability.



Turning back to the SELECT statement used in the SQLXMLQuery.php script discussed in the Using Oracle SQL/XML Generation Functions section earlier in this chapter, you might create an XMLType view based on that statement as shown below.


Sample Code
  1. CONN /AS sysdba
  2.         GRANT CREATE ANY VIEW TO xmlusr;
  3.         CONN xmlusr/xmlusr;
  4.         CREATE VIEW EmpsXML AS
  5.                 SELECT XMLELEMENT("EMPLOYEES",
  6.                         XMLAgg(
  7.                         XMLELEMENT("EMPLOYEE",
  8.                         XMLATTRIBUTES(employee_id AS "id"),
  9.                         XMLFOREST(last_name AS "ENAME", salary AS "SALARY")
  10.                         )
  11.                   )
  12.            ) AS xmlcol,
  13.          department_id AS dept_id
  14.         FROM hr.employees GROUP BY department_id;
Copyright exforsys.com


In this example, you start by granting the CREATE VIEW privilege to the xmlusr database schema and then, when connected as xmlusr/xmlusr, create the EmpsXML view based on the query that uses SQL/XML functions to generate XML from the data stored in the hr.employees relational table.


The good thing about the EmpsXML view is that it hides the details of generatingan employees XML document, thus letting you write simpler and more readable queries. With it, the query used in the SQLXMLQuery.php script might be rewritten as follows:


$query = 'SELECT xmlcol as RESULT FROM EmpsXML WHERE dept_id=:deptid';

Before running the updated SQLXMLQuery.php script, make sure to specify the xmlusr/xmlusr schema in the oci_connect function at the beginning of the script as follows:

$rsConnection = oci_connect('xmlusr', 'xmlusr', '//localhost/orcl'))


Also, you might rewrite the query string used in the DBServerXSLTrans.php script discussed in the Performing XSLT Transformations inside the Database section earlier in this chapter as follows:


$query = 'SELECT XMLtransform(x.xmlcol, (SELECT stylesheet FROM XSLTstylesheets WHERE id = 1)).getStringVal() AS result FROM (SELECT * FROM EmpsXML WHERE dept_id=:deptid) x';


As you can see, the above query is three times smaller than the one originally used in the DBServerXSLTrans.php script.


Next Page: Creating XML Schema-Based XMLType Views


Read Next: Performing DML Operations on XML Schema Based 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 - 2009 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape