XQuery in Oracle XML DB

Oracle XML DB is the Oracle XML Database software suite. Essentially , Oracle XML DB is a fancy name for all of the XML technology included with Oracle Database software. This includes all the tools and gadgets introduced in Chapter 5, plus a few other things such as handling of XQuery and XML Schemas. Chapter 5 covered the basics of XMLType data types in Oracle Database, plus basic coverage of XML data access coding standards, such as SQL/XML and changes to XML documents. Now you can expand on Oracle Database implementation of XML coverage and include some specific tools and functions, which can help execute XQuery queries in an Oracle database.

Oracle XML DB supports XQuery using two SQL/XML functions: XMLQuery and XMLTable . There is also a very simple-to-use SQL*Plus command called XQUERY , which allows SQL*Plus to be used in a very similar fashion to that of Saxon. XQUERY allows SQL*Plus to be used as an Oracle built-in shell tool (or command-line interpreter). The objective of SQL/XML is a combination of the best of both worlds of SQL and XML. The XMLQuery and XMLTable commands allow building of XML from relations, building of relations from XML, and querying of relational data as if it were XML data.

Oracle XML DB approaches XML data in two ways:

• Unstructured XML:  There is no schema and XPath expressions evaluate XML data functionally (based on an XPath expression). An XML DOM structure is created for parsing, and any changes involve writing the entire XMLType data type structure back to disk (after in-memory changes).

• Structured XML:  This is XML data with an Oracle relational table schema to back it up through an XML Schema definition (or XML Schema collections). This allows for translation between XML and SQL queries plus potentially partial updates. I will ignore this method for now, as being too Oracle Databasespecific with respect to this part of this book. Additionally, XML Schemas are covered later on in this book.

Oracle database software uses various SQL coding tools most are one form or another of a shell tool called SQL*Plus. SQL*Plus has a command called XQUERY , which accepts an expression that is a little of XPath and a little of SQL put together.

The following example selects all rows from the REGION table:

   SET LONG 2000 WRAP ON LINESIZE 5000 xquery for $n in ora:view("REGION") return $n /   

The result of the preceding query is shown in Figure 11-16.

Figure 11-16: Using the SQL*Plus XQUERY command

The query shown in Figure 11-16 will not function in SQL*Plus in Oracle Database 10.2.

The XMLQuery and XMLTable functions allow for a powerful combination of SQL and XML capabilities. Using the XMLQuery and XML Table functions you can essentially pass data backward and forward between relational and XML data structures. These two functions are a little too complex for a book of this nature, and a lot of XML functionality for Oracle Database is covered in Chapter 5.