Bringing Together the Power of SQL and XML with XQuery

From WikiContent

Revision as of 19:26, 11 January 2010 by Yul (Talk | contribs)
Jump to: navigation, search

The XQuery language provides an efficient means of querying, constructing, and transforming XML data. Using XQuery, you can easily bring together pieces of information from disparate data sources, accessing both database and non-database data within the same query. The only requirement is that the data you’re accessing must be expressible in XML format. Say, it may be relational data, office documents, or simply plain text files.

Oracle Database provides a standard-compliant XQuery implementation making it possible for you to access XML-presentable data wherever it is found: stored in a database, located in a file system, or even retrieved by a search engine. For example, you might issue an XQuery query that will construct XML from relational data or will transform an XML document stored in a file system into HTML code.

Using Oracle XQuery is straightforward. You pass an XQuery expression as the argument to a SQL/XML standard function such as XMLQuery or XMLTable, thus taking advantage of XQuery through standard SQL interfaces. Being used as the argument of an SQL function invoked within an SQL statement, an XQuery query returns either XML or relational data that you can further process in the parent SQL query. For example, you might define an SQL join query that will connect a Google search’s result decomposed into relational rows by XMLTable, with the data stored in a relational table, thus seamlessly integrating relational and Web data.

The following example illustrates how you might construct XML from relational data stored in the HR/employees Oracle Database demonstration table:

SELECT XMLQuery(

'for $j in 1
 return (
 <EMPLOYEES> {
 for $i in ora:view("HR", "employees")/ROW
  where $i/EMPLOYEE_ID <= 102
  order by $i/SALARY 
  return (<EMPLOYEE>
               <EMPID>{xs:string($i/EMPLOYEE_ID)}</EMPID>
               <EMPNAME>{xs:string($i/LAST_NAME)}</EMPNAME>
               <SAL>{xs:integer($i/SALARY)}</SAL>
          </EMPLOYEE>)} </EMPLOYEES>)'
 RETURNING CONTENT) AS EMPLOYEES FROM DUAL;

As you can see, XQuery is similar in many ways to SQL. Just like SQL queries can contain different clauses, such as SELECT, WHERE, and GROUP BY, XQuery queries can contain FOR, LET, WHERE, ORDER BY, and RETURN clauses, which are collectively referred to as FLWOR.

The XML generated by the above XQuery query should look like this:

<EMPLOYEES>
 <EMPLOYEE>
  <EMPID>101</EMPID>
  <EMPNAME>Kochhar</EMPNAME>
  <SAL>17000</SAL>
 </EMPLOYEE>
 <EMPLOYEE>
  <EMPID>100</EMPID>
  <EMPNAME>King</EMPNAME>
  <SAL>24000</SAL>
 </EMPLOYEE>
</EMPLOYEES>

Now if you save this XML to a file, say, emp.xml in the document directory of your web server, you could issue the following XQuery query shredding this XML into relational data:

SELECT * FROM XMLTable( 'for $i in $rslt/EMPLOYEES/EMPLOYEE

 return $i'
 PASSING xmlparse (document httpuritype
 ('http://localhost/emp.xml').getCLOB()) as "rslt"
 COLUMNS empid VARCHAR2(100) PATH 'EMPID',
         empname VARCHAR2(100) PATH 'EMPNAME',
         sal NUMBER PATH 'SAL');

The above should produce the following output:

MPID EMPNAME SAL


101 Kochhar 17000 100 King 24000

Just like with regular SQL statements, you can improve flexibility and execution performance of XQuery expressions by using bind dynamic variables. Using bind variables reduces parsing overhead, thus increasing your performance and scalability as well. You can bind dynamic variables to an XQuery expression within the PASSING clause, which is available in both the XMLQuery and XMLTable SQL functions.

It’s interesting to note that Oracle Database allows you to monitor the performance of XQuery expressions, examining the execution path used by the SQL optimizer in the same way you would do it when dealing with a regular SQL statement. In Oracle SQL*Plus, you set the AUTOTRACE system variable to print the execution path used by the SQL optimizer. For further details, you can refer to the SQL*Plus User’s Guide and Reference book in the Oracle Database documentation.

As you no doubt have realized, XQuery is similar to SQL but is not limited to working only with database data – any data source expressible in XML can be reached with your XQuery query.

Personal tools