Using XMLType for Handling XML Data in Oracle Database

Using XMLType for Handling XML Data in the Database

Being an object type, XMLType can not only be used to store XML data in the database but also to operate on that data via its built-in methods. Regardless of the storage model you choose, XMLType provides a set of XML-specific methods to operate on XMLType instances.

The most commonly used methods of XMLType are listed in the following table:

XMLType method	Description
existsNode	Checks whether the XML document in a given XMLType instance contains a node that matches the XPath expression passed as the parameter. If the specifi ed node is found, it returns 1; otherwise, it returns 0.

extract	Extracts a node or nodes from the XML document in an XMLType instance, based on the XPath expression passed as the parameter. Returns the result nodes as an XMLType instance.

createSchemaBasedXML	Explicitly associates an XML document in an XMLType instance with a registered XML schema specifi ed in the parameter. You might want to perform this operation when inserting an XML document into an XML schema-based XMLType column or table.

schemaValidate	Validates an XML document in an XMLType instance against an XML schema specifi ed in the parameter. On success, the status of the document is changed to VALIDATED; otherwise, an error is raised.

transform	Transforms an XML document in an XMLType instance with the XSL stylesheet specifi ed in the parameter. Returns the resultant document as an XMLType instance.

You saw an example of using an XMLType method in the preceding sample application. In particular, in the DBServerXSLTrans.php script discussed in the Performing XSLT Transformations inside the Database section you use the getStringVal method of XMLType to retrieve the generated XHTML data as a VARCHAR value, so that it can then be loaded in a DOMDocument instance. If you recall, the query used in the DBServerXSLTrans.php script looks as follows:

   $query = 'SELECT XMLtransform(x.xmlcol,
   (SELECT stylesheet FROM XSLTstylesheets WHERE
      id = 1)).getStringVal()
   AS result FROM
   (SELECT XMLELEMENT("EMPLOYEES",
         XMLAgg(
            XMLELEMENT("EMPLOYEE",
            XMLATTRIBUTES(employee_id AS "id"),
            XMLFOREST(last_name AS "ENAME", salary AS "SALARY")
         )
      )
   ) AS xmlcol
   FROM hr.employees WHERE department_id=:deptid) x'; 
   
%26nbsp%3B%20%26nbsp%3B%24query%20%3D%20%27SELECT%20XMLtransform%28x.xmlcol%2C%0D%0A%26nbsp%3B%20%26nbsp%3B%28SELECT%20stylesheet%20FROM%20XSLTstylesheets%20WHERE%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3Bid%20%3D%201%29%29.getStringVal%28%29%0D%0A%26nbsp%3B%20%26nbsp%3BAS%20result%20FROM%0D%0A%26nbsp%3B%20%26nbsp%3B%28SELECT%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%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%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%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%29%0D%0A%26nbsp%3B%20%26nbsp%3B%29%20AS%20xmlcol%0D%0A%26nbsp%3B%20%26nbsp%3BFROM%20hr.employees%20WHERE%20department_id%3D%3Adeptid%29%20x%27%3B

To see another XMLType method in action, namely transform, you might rewritethe above SQL statement as follows:

   $query = 'SELECT x.xmlcol.transform((SELECT stylesheet FROM
   XSLTstylesheets WHERE id = 1)).getStringVal()
   AS result FROM
   (SELECT XMLELEMENT("EMPLOYEES",
      XMLAgg(
      XMLELEMENT("EMPLOYEE",
      XMLATTRIBUTES(employee_id AS "id"),
      XMLFOREST(last_name AS "ENAME", salary AS "SALARY")
      )
      )
   ) AS xmlcol
   FROM hr.employees WHERE department_id=:deptid) x';  
   
%26nbsp%3B%20%26nbsp%3B%24query%20%3D%20%27SELECT%20x.xmlcol.transform%28%28SELECT%20stylesheet%20FROM%0D%0A%26nbsp%3B%20%26nbsp%3BXSLTstylesheets%20WHERE%20id%20%3D%201%29%29.getStringVal%28%29%0D%0A%26nbsp%3B%20%26nbsp%3BAS%20result%20FROM%0D%0A%26nbsp%3B%20%26nbsp%3B%28SELECT%20XMLELEMENT%28%22EMPLOYEES%22%2C%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3BXMLAgg%28%0D%0A%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%3BXMLATTRIBUTES%28employee_id%20AS%20%22id%22%29%2C%0D%0A%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%29%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20%29%0D%0A%26nbsp%3B%20%26nbsp%3B%29%20AS%20xmlcol%0D%0A%26nbsp%3B%20%26nbsp%3BFROM%20hr.employees%20WHERE%20department_id%3D%3Adeptid%29%20x%27%3B%20

In the above query, you use the transform XMLType method as an alternative to the XMLtransform SQL function used in the original query. Since both transform and XMLtransform have the same functionality, the above queries will produce the same general result.

NOTE: XMLtransform is not the only example of an SQL function providing the same functionality as the appropriate XMLType method. In fact, Oracle XML DB provides analogous SQL functions for many XMLType methods. For example, XMLType methods extract and existsNode can be used instead of the SQL functions having the same names.

As you can see, the above queries operate on relational data, and transform it into XML format with SQL/XML generation functions. Before looking at the approaches you can take to retrieve XML data stored in the database natively, however, it would be a good idea to look at how you can create an XMLType storage in Oracle XML DB. The following section discusses how you can do this with the help of the XML Schema feature.

Using XMLType for Handling XML Data in the Database

Tags