Oracle XQuery to Access XML/HTML Content From the Database

 

By Yuli Vasiliev

 

Oracle XQuery is one of the most widely used facilities provided by Oracle XML DB, allowing you to issue XQuery queries right from within SQL statements. It works like this. You pass an XQuery expression to a SQL/XML function, such as XMLQuery or XMLTable, querying either XML-expressible data or relational data. The data you query can be in the database, on the Web, or generated on the fly. The returned results can be shredded into relational rows and joined with the other data in the same SQL statement. Since things are best understood by example, let’s look at one.

 

Turning to an example, suppose you have a bonus.xml document available through http, which contains the list of employees entitled to a bonus. This document could look like this:

 

<EMPLOYEES>

<EMPLOYEE dept=”50″>

<LAST_NAME>Jones</LAST_NAME>

<BONUS>2000</BONUS>

</EMPLOYEE>

<EMPLOYEE dept=”110″>

<LAST_NAME>Higgins</LAST_NAME>

<BONUS>2500</BONUS>

</EMPLOYEE>

 

…

 

</EMPLOYEES>

 

For the purpose of this example, you can use your localhost web server to expose the above document through http.

 

The following SQL statement illustrates how you can access the above bonus.xml document stored outside of the database and the departments database table from the HR demonstration schema, joining the retrieved results together: As you might guess, this example assumes you have demonstration schema HR installed and unlocked in your Oracle database:

 

SELECT XMLQuery(‘<EMPLOYEES>

{for $i in $h//EMPLOYEE

for $j in fn:collection(“oradb:/HR/DEPARTMENTS”)/ROW

where $j/DEPARTMENT_ID = $i/@dept

return   <EMPLOYEE dept=”{$j/DEPARTMENT_NAME}”>

<LAST_NAME>{$i/LAST_NAME}</LAST_NAME>

<BONUS>{$i/BONUS}</BONUS>

</EMPLOYEE>}

</EMPLOYEES>’

PASSING xmlparse (document httpuritype (‘http://localhost:8888/bonus.xml’).getCLOB()) as “h”

RETURNING CONTENT) AS employees

FROM DUAL;

 

This is a concise example of how you can generate an XML document with SQL/XML functions, deriving and combining data from different sources. In the expression passed to the XMLQuery function as argument, you define the desired structure for the document being created, incorporating XQuery expressions enclosed by braces.

 

If the above query generates error: ‘network access denied by access control list (ACL)’, you have to grant the connect privilege for localhost to the HR user. You can do that being connected as the SYS user, with the following PL/SQL code. This example assumes your localhost web server listens on port 8888:

 

BEGIN

DBMS_NETWORK_ACL_ADMIN.create_acl (

acl          => ‘acl_file.xml’,

description  => ”,

principal    => ‘HR’,

is_grant     => TRUE,

privilege    => ‘connect’,

start_date   => SYSTIMESTAMP,

end_date     => NULL);

 

COMMIT;

END;

/

 

BEGIN

DBMS_NETWORK_ACL_ADMIN.assign_acl(

acl => ‘acl_file.xml’,

host => ‘localhost’,

lower_port =>8888,

upper_port => NULL);

 

COMMIT;

END;

/

 

After the above code has completed successfully, you can connect as HR again and reissue the query. The generated XML document should look like this:

 

EMPLOYEES

——————————–

<EMPLOYEES>

<EMPLOYEE dept=”Shipping”>

<LAST_NAME>Jones</LAST_NAME>

<BONUS>2000</BONUS>

</EMPLOYEE>

<EMPLOYEE dept=”Accounting”>

<LAST_NAME>Higgins</LAST_NAME>

<BONUS>2500</BONUS>

</EMPLOYEE>

</EMPLOYEES>

 

So you looked at how you can generate an XML document on the fly, gathering XML data and relational data within a single SQL statement.

 

About the Author

 

Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, business intelligence (BI), databases, and service-oriented architecture (SOA). He is the author of Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting (Packt, 2010) and PHP Oracle Web Development (Packt, 2007) as well as a series of other books on the Oracle technology.