SQL/XML and XPath Rewrite
SQL/XML provides a series of functions to integrate XML functionality to SQL processing,below is the list of such functions:
APPENDCHILDXML
DELETEXML
DEPTH
EXTRACT (XML)
EXISTSNODE
EXTRACTVALUE
INSERTCHILDXML
INSERTXMLBEFORE
PATH
SYS_DBURIGEN
SYS_XMLAGG
SYS_XMLGEN
UPDATEXML
XMLAGG
XMLCDATA
XMLCOLATTVAL
XMLCOMMENT
XMLCONCAT
XMLFOREST
XMLPARSE
XMLPI
XMLQUERY
XMLROOT
XMLSEQUENCE
XMLSERIALIZE
XMLTABLE
XMLTRANSFORM
Also, XMLType is important in Oracle XML DB, which I have discussed in the last post.
XMLType is a system-defined opaque type for handling XML data. It has predefined member functions on it to extract XML nodes and fragments.
You can create columns of XMLType and insert XML documents into it. You can also generate XML documents as XMLType instances dynamically using the SYS_XMLGEN and SYS_XMLAGG SQL functions.
Table 198-1 summarizes functions and procedures of the XMLType.
Table 198-1 XMLTYPE Subprograms
| Method | Description |
|---|---|
|
Creates a non schema based XML from the input schema based instance. |
|
|
Creates a schema based |
|
|
Static function for creating and returning an |
|
|
Takes a |
|
|
Takes a |
|
|
Returns the value of the |
|
|
Returns the value of the |
|
|
Returns the namespace for the top level element in a schema based document. |
|
|
Returns the value of the |
|
|
Returns the root element of the input instance. Returns |
|
|
Returns the XML schema URL if the input is an XML Schema based. |
|
|
Returns the value of the |
|
|
Checks if the input |
|
|
Returns 1 or 0 indicating if the input |
|
|
Checks if the input instance is schema valid according to the given schema URL. |
|
|
Checks if the instance has been validated against the schema. |
|
|
Validates the input instance according to the XML Schema. Raises error if the input instance is non-schema based. |
|
|
Sets the schema valid flag to avoid costly schema validation. |
|
|
Converts the |
|
|
Takes an |
|
|
Constructs an instance of the |
The SQL/XML SQL functions and their corresponding XMLType methods allow XPath expressions to be used to search collections of XML documents and to access a subset of the nodes contained within an XML document.
Oracle XML DB provides two ways of evaluating XPath expressions that operate on XMLType columns and tables, depending on the XML storage method used:
- Structured-storage XML data: Oracle XML DB attempts to translate the XPath expression in a SQL/XML function into an equivalent SQL query. The SQL query references the object-relational data structures that underpin a schema-based XMLType. This process is referred to as XPath rewrite. It can occur when performing queries and UPDATE operations.
- Unstructured-storage XML data: Oracle XML DB evaluates the XPath expression using functional evaluation. Functional evaluation builds a DOM tree for each XML document, and then resolves the XPath programmatically using the methods provided by the DOM API. If the operation involves updating the DOM tree, the entire XML document has to be written back to disc when the operation is completed.
One example of XPath Rewrite is as following:
SELECT OBJECT_VALUE FROM mypurchaseorders p WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/Company') = 'Oracle';
will become:
SELECT VALUE(p) FROM mypurchaseorders p WHERE p.xmldata.Company = 'Oracle';