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.

CONN /AS sysdba
   GRANT CREATE ANY VIEW TO xmlusr;
   CONN xmlusr/xmlusr;
   CREATE VIEW EmpsXML AS
      SELECT XMLELEMENT("EMPLOYEES",
         XMLAgg(
         XMLELEMENT("EMPLOYEE",
         XMLATTRIBUTES(employee_id AS "id"),
         XMLFOREST(last_name AS "ENAME", salary AS "SALARY")
         )
        )
      ) AS xmlcol,
      department_id AS dept_id
   FROM hr.employees GROUP BY department_id; 
   
CONN%20%2Fas%20sysdba%0D%0A%26nbsp%3B%20%26nbsp%3BGRANT%20CREATE%20ANY%20VIEW%20TO%20xmlusr%3B%0D%0A%26nbsp%3B%20%26nbsp%3BCONN%20xmlusr%2Fxmlusr%3B%0D%0A%26nbsp%3B%20%26nbsp%3BCREATE%20VIEW%20EmpsXML%20AS%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3BSELECT%20XMLELEMENT%28%22EMPLOYEES%22%2C%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3BXMLAgg%28%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3BXMLELEMENT%28%22EMPLOYEE%22%2C%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3BXMLATTRIBUTES%28employee_id%20AS%20%22id%22%29%2C%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3BXMLFOREST%28last_name%20AS%20%22ENAME%22%2C%20salary%20AS%20%22SALARY%22%29%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%29%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%20%20%29%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20%29%20AS%20xmlcol%2C%0D%0A%20%20%26nbsp%3B%20%26nbsp%3B%20department_id%20AS%20dept_id%0D%0A%26nbsp%3B%20%26nbsp%3BFROM%20hr.employees%20GROUP%20BY%20department_id%3B

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.