Using Oracle XQuery query, build, and transform XML

oracle|xml| Conversion

In Oracle's 10g 2nd edition, Oracle introduced a full-featured, self-contained XQuery engine integrated with the database that can be used to complete various tasks related to developing XML-enabled applications. XQuery is a query language for processing XML data models, which can actually manipulate data of any type that can be expressed in XML. Although Oracle XQuery implementations allow you to work with database data and external data sources, Oracle XML DB can typically improve performance significantly in processing structured data stored in the database.

The examples provided in this article demonstrate not only where and how to use XQuery queries, build and transform XML, but also how to monitor and analyze performance execution of XQuery expressions to find more efficient ways to handle the same workload.

Building XML based on relational data

You might want to build XML based on relational data, if needed (for example, to send a result to a WEB service). To complete this task in the version prior to 10g 2nd of the Oracle database, you typically need to use sql/xml build functions such as XmlElement, Xmlforest, and XMLAgg (). XQuery will be more efficient than these functions in the 2nd edition of Oracle database G. Specifically, using the Ora:view XQuery function inside an XQuery expression, you can query an existing relational table or view and build XML on the fly, eliminating the need to explicitly create an XML view through relational data. The Pl/sql code in Listing 1 demonstrates how to use Ora:view to build an XML document from the data stored in the default Employee Relations table of the sample database schema HR.

Listing 1: Using Ora:view to create XML based on relational data

BEGIN
IF (dbms_xdb. CreateFolder ('/public/employees ')) THEN
Dbms_output. Put_Line (' Folder is created ');
ELSE
Dbms_output. Put_Line (' Cannot create folder ');
End IF;
COMMIT;
End;
/

DECLARE
XmlDoc XmlType;
BEGIN
SELECT Xmlquery (
' For $j in 1
Return (
{
For $i in Ora:view ("HR", "Employees")/row
where $i/employee_id <= 102
Return (
{xs:string ($i/employee_id)}
{xs:string ($i/last_name)}
{Xs:integer ($i/salary)}
)} )'
Returning CONTENT) into the xmldoc from DUAL;
IF (dbms_xdb. Createresource ('/public/employees/employees.xml ', xmldoc)) THEN
Dbms_output. Put_Line (' Resource is created ');
ELSE
Dbms_output. Put_Line (' cannot create resource ');
End IF;
COMMIT;
End;
/

In the first pl/sql procedure in Listing 1, you just created a new folder in the XML repository. In the Repository folder, you will then store the XML document that was created in the second Pl/sql procedure shown here. The second Pl/sql process first emits a SELECT statement that uses the Xmlquery SQL function to build XML based on relational data. For XQuery expressions (which xmlquery use as parameters here), be aware of the Ora:view XQuery functions that are used in nested FLWOR expressions. In this example, Ora:view gets two input parameters, "HR" and "Employees," which indicate that the function queries the employee table that is part of the HR database schema. Therefore, Ora:view will return a sequence of employee XML documents that represents the Hr.employees table row. However, to save space in the resulting document, only the first three employee records are passed to the result sequence. This is accomplished by specifying $i/employee_id <= 102 in the WHERE clause of the FLWOR expression. Note the xs:string () and Xs:integer () XQuery type expressions used in the return clause of the FLWOR expression. In fact, the two XQuery expressions used here not only convert the XML node values to the appropriate types, but also extract the node values. Subsequently, the generated employee XML document is saved as a employees.xml to the/public/employees XML repository folder that was previously created in the other Pl/sql procedure in Listing 1. To ensure that this operation is complete, you can execute the following query:

SELECT xmlquery (' for $i in Fn:doc ("/public/employees/employees.xml")
Return
$i '
Returning CONTENT) as result from DUAL;

The query should produce the following output:

-
King
24000


?
Kochhar
17000


102
De Haan
17000

In the above XQuery, the Fn:doc XQuery function is used to access a single XML document stored in an Oracle XML DB repository. But what if you want to work with XML documents that have the same or similar structure (stored in the same XML repository folder)? In this case, another XQuery function (that is, fn:collection) that is used to process the XML repository resource might come in handy. A few examples of how to use the Fn:collection XQuery function are described later in this article.

Querying XmlType data

XQuery enables you to manipulate data based on XML schemas and not based on schemas. The following example demonstrates how to use the XMLTable function to query the XmlType table based on the PurchaseOrder XML schema from the OE demo database schema.

SELECT Ttab. Column_value as OrderTotal from PurchaseOrder,
XMLTable (
' For $i In/purchaseorder
where $i/user = "Eabel"
Return

{$i/reference}

{Fn:sum (for $j in $i/lineitems/lineitem/part
Return ($J/@Quantity * $j/@UnitPrice))}

'
Passing Object_value
) Ttab;

In the preceding example, you use the Object_value virtual column in the passing clause of the XMLTable function to pass the PurchaseOrder table as a context item to the XQuery expression used here. An XQuery expression calculates the total of each purchase order that a user Eabel requests and generates a OrderTotal XML element for each order that is processed. To access the generated XML, use the Column_value virtual column in the SELECT list. The final output should look like the following:

ORDERTOTAL
-------------------------------------------------------------

Eabel-20021009123338324pdt
1328.05


Eabel-20021009123335791pdt
2067.15


Eabel-20021009123336251pdt
289.6


Eabel-20021009123336382pdt
928.92

To achieve the same final result, you can use the Xmlquery function instead. However, if you pass the XQuery expression parameter used in the previous example to Xmlquery (see below):

SELECT xmlquery (' for $i In/purchaseorder
where $i/user eq "Eabel"
Return
{$i/reference}

{Fn:sum (for $j in $i/lineitems/lineitem/part
Return ($J/@Quantity * $j/@UnitPrice))}

'
Passing Object_value
Returning CONTENT)
From PurchaseOrder;

The empty sequence returned by the XQuery expression is joined to the PurchaseOrder table, which is included in the query's total result set. In effect, this means that the output will not only contain the OrderTotal element generated for the orders that the user Eabel requested, but also contain the empty rows generated for all other orders stored in the PurchaseOrder table (the PurchaseOrder table contains 132 rows by default). One way to exclude empty rows from the result set is to use the Existsnode SQL function in the WHERE clause of the SELECT statement, rather than using the WHERE clause in an XQuery expression, as follows:

SELECT xmlquery (' for $i In/purchaseorder
Return
{$i/reference}

{Fn:sum (for $j in $i/lineitems/lineitem/part
Return ($J/@Quantity * $j/@UnitPrice))}

'
Passing Object_value
Returning CONTENT) as OrderTotal
From PurchaseOrder
WHERE Existsnode (object_value, '/purchaseorder[user = ' eabel '] ') = 1;

The above query generates the same output as the XMLTable example at the beginning of this section.

Querying XML data in an Oracle XML DB repository

To access the XML data stored in the Oracle XML DB Repository, Oracle XQuery introduces the Fn:doc and Fn:collection XQuery functions. With Fn:doc, you can query a single XML document stored in an XML repository, while Fn:collection allows you to access multiple XML documents stored in the same repository folder.

As demonstrated by the example described earlier in this article (see Building XML with relational data), using Fn:doc is straightforward. It gets a string representing the Repository file resource (URI) and returns the document that the URI points to. To understand the role of the fn:collection XQuery function, there should be at least two library files in the same folder. If you have already run the code in Listing 1, you have created the/public/employees repository folder and stored the Employees.xml file in it. Therefore, you will need to create at least one more XML file in the folder before you can try Fn:collection. The Pl/sql code in Listing 2 builds XML based on the relational data stored by the Dept and EMP Tables that Scott/tiger demonstrates the database schema, and then saves the resulting XML document as a acc_dept.xml to the/public/employees information base Clips. To run the Pl/sql procedure in Listing 2, make sure you are logged on as a scott/tiger.

Listing 2: Building XML based on relational data and saving it to an XML repository

DECLARE
XmlDoc xmltype;
BEGIN
SELECT xmlquery (
' for $j in Ora:view ("SCOTT", "dept")/row
where $j/deptno = ten
Return (
{$j/deptno,
$j/dname}
{
for $i in Ora:view ("SCOTT", "emp")/row
where $i/d Eptno = $j/deptno
return (

{$i/empno,
$i/ename,
$i/sal}
)}

) '
returning CONTENT ' into the xmldoc from DUAL;
IF (dbms_xdb. Createresource ('/public/employees/acc_dept.xml ', xmldoc)) THEN
Dbms_output. Put_Line (' Resource is created ');
ELSE
Dbms_output. Put_Line (' cannot create resource ');
End IF;
COMMIT;
End;
/

At this point, the/public/employees repository folder should contain two files: Acc_dept.xml (generated by the Pl/sql code in Listing 2) and Employees.xml file (generated by the code in Listing 1). Because these XML documents are stored in the same repository folder, you can use the Fn:collection function to access employee information stored in two XML documents. However, although these XML documents contain employee XML elements (which actually have the same structure), the structure of the XML document itself is very different. In Employees.xml, the document root element is employees, and Acc_dept.xml uses DEPARTMENT as the root element. To work around this problem, you can use XPath//constructs with XQuery to navigate to a node in an XML document without having to specify the exact path of that node. The following example shows how to use XPath//constructs in an XQuery expression:

SELECT Xmlquery (
' For $i in Fn:collection ("/public/employees")//employee
where $i/sal >= 5000
ORDER BY $i/ename
Return
$i '
Returning CONTENT) from DUAL;

The construct should produce the following output:

102
De Haan
17000


7839
KING
5000


Kin
G
24000


Kochhar

17000

As you can see, the above output contains the employee XML elements obtained from employees.xml and acc_dept.xml that represent employees with a compensation greater than or equal to 5,000 dollars.

Decomposing XML into relational data

If your application processes relational data rather than XML, and the data you need to access is stored in XML format, it can be useful to decompose the XML into relational data. To continue with the example in the previous section, you can use the SQL function XMLTable to decompose an employee XML element into a single column of a virtual table, as follows:

SELECT Emps.empno,emps.ename, emps.sal from
XMLTable (
' For $i in Fn:collection ("/public/employees")//employee
where $i/sal >= 5000
Return
$i '
COLUMNS empno number PATH '/employee/empno ',
Ename VARCHAR2 () PATH '/employee/ename ',
Sal number PATH '/employee/sal ') emps;

The query produces the following output:
EMPNO ename SAL
----- -------------- ----------
7839 KING 5000
King 24000
17000 Kochhar
102 De Haan 17000

Querying external data sources

With XQuery, you can generate XML documents based on XML data and non-XML data that can be represented in XML, regardless of location: stored in a database, placed on a Web site, created on the fly, or stored in a file system. Note, however, that Oracle XML DB provides very high performance and scalability for XML operations on data stored in the database. Therefore, if you have complete control over the data being processed, it is a good idea to move it to the database.

As you learned from the previous example, in Oracle XQuery implementations, the doc and collection XQuery functions are used to access the XML documents stored in the Oracle XML DB repository. You can dynamically bind an external data source through the passing clause in XMLTable and xmlquery SQL functions. Consider the following example. Let's say your company pays bonuses to employees who are committed to XQ projects. Therefore, the Finance Department publishes the Empsbonus.xml file, which contains a list of employees eligible for bonuses and the amount of bonuses entered in the list for each employee. The Empsbonus.xml file might look like the following:

100
1200


101
1000

In practice, the above XML files may be placed on a Web site (and therefore available over the Internet), stored as files in a local file system, or stored as file resources in an Oracle XML DB repository. For the purposes of this example, the file is located on the Web site. For simplicity, you can create an employee folder in the directory where the Web server stores documents that you can see from the Web, and then insert the Empsbonus.xml file in that folder so that you can access the Empsbonus.xml file through the following URL:

Http://localhost/employees/empsbonus.xml
Next, suppose you need to create a report based on the data stored in the Empsbonus.xml document. In this report, you may want to include not only the amount of bonus displayed in the list, but also the employee ID of each employee, as well as his or her full name. Therefore, you can first use the following query to generate a new XML document (assuming you are connected as a hr/hr):

SELECT Xmlquery (
' For $k in 1
Return (
{for $i in Ora:view ("Employees")/row,
$j in $emps/employees/employee
where $i/employee_id = $j/empno
Return (
{xs:string ($i/employee_id)}
{xs:string (Fn:concat ($i/first_name, "", $i/last_name))}
{Xs:integer ($j/bonus)}
)} )'
Passing Xmlparse (document Httpuritype
(' Http://localhost/employees/empsbonus.xml '). Getclob ()) as "Emps"
Returning CONTENT). Getstringval () as result from DUAL;

The above query is an example of how to use XQuery to generate XML documents based on XML and non-XML data (retrieved in different ways from different data sources). Specifically, use the Ora:view () function to access the default Employees relational table in HR demo mode and use the Httpuritype () function in the passing clause to access the Empsbonus.xml document with HTTP. The new XML document is then constructed in the return clause of the FLWOR expression. Finally, you will get the following XML document:

100
Steven King
1200


101
Neena Kochhar
1000

Troubleshoot performance issues

As you learned from the previous section, XQuery is an efficient way to query the XML content stored in an Oracle database-whether you are working with locally stored xmltype data or querying an XML view built on relational data. However, depending on the type of storage used for the data, the performance of an XQuery expression can be very different. In particular, Oracle XML DB can optimize XQuery expressions built on the Sql/xml view created by the Ora:view function. For XML data stored in a XmlType table or column, XQuery optimization can only be performed on xml-based schema-xmltype data that is stored using structured (object-relational) storage technologies.

The storage model that you choose is not the only factor that affects the performance of XQuery expression execution. In some cases, the structure of an XQuery expression itself can also cause performance problems. To monitor the performance of an XQuery expression, you can print and check the associated EXPLAIN plan. In Sql*plus, you can print the execution path used by the SQL optimizer simply by setting the AUTOTRACE system variable. However, to do so, be sure to create the Plustrace role and then grant it to the users who are connected to the database. For information about how to do this, see the "Adjusting Sql*plus" chapter of the "Sql*plus User's Guide and reference" in the Oracle Database 10g 2nd (10.2) documentation. The following example demonstrates how to benefit from checking the execution plan generated by EXPLAIN. Suppose you have granted the Plustrace role to the default user OE, log in as Oe/oe and run the following query:

SET Autotrace on EXPLAIN

SELECT COUNT (*)
From Oe.purchaseorder, XMLTable (
' For $i In/purchaseorder/user
where $i = "Cjohnson"
Return $i '
Passing Object_value) Ptab;

This produces the following output:
COUNT (*)
----------
9

Execution Plan
----------------------------------------------------
Plan Hash value:4046110317

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 226 | 29 (0) | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 226 | | |

| 2 | NESTED LOOPS | | 10782 | 2379K | 29 (0) | 00:00:01 |

|* 3 | TABLE ACCESS Full | PurchaseOrder | 1 | 226 | 5 (0) | 00:00:01 |

| 4 | COLLECTION iterator p| xmlsequencefromx| | | | |

predicate information (identified by Operation ID):
---------------------------------------------------

3-filter (Sys_checkacl) ("Acloid", "ownerID", XmlType ('<>
...

You may not be satisfied with the execution plan generated for the above query. In particular, the number of rows processed can be very large. Because the primary goal of SQL tuning is to avoid accessing rows that have no effect on the results, you may want to continue tuning the query to optimize performance. After you have modeled the XPath expression contained in the query, you can retry it again, as follows:

SELECT COUNT (*)
From Oe.purchaseorder, XMLTable (
' For $i In/purchaseorder
where $i/user = "Cjohnson"
return $i/user '
Passing Object_value) Ptab;

This time, the output should look like the following:
COUNT (*)
----------
9

Execution Plan
---------------------------------------------------
Plan Hash value:3411896580

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 7 (0) | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 29 | | |

| 2 | NESTED LOOPS | | 1 | 29 | 7 (0) | 00:00:01 |

| 3 | FAST DUAL | | 1 | | 2 (0) | 00:00:01 |

|* 4 | TABLE ACCESS Full | PurchaseOrder | 1 | 29 | 5 (0) | 00:00:01 |

predicate information (identified by Operation ID):
---------------------------------------------------

4-filter ("PurchaseOrder".) sys_nc00022$ "= ' Cjohnson ' and
Sys_checkacl ("Acloid", "ownerID", XmlType) ('<>
...

As you can see, the queries shown above generate the same final results, but their execution plans are not the same. Looking at the XQuery expression in the last example, you may notice that it iterates over the top-level purchaseorder element, where each purchaseorder element represents a row in a table based on the PurchaseOrder xmltype pattern. This means actually overriding the XQuery expression to overlap the rows in the underlying object table (used to store the decomposed PurchaseOrder document). This method has better performance than an XML element that does not represent a single row in the underlying table.

In some cases, however, it is difficult to see which construct of an XQuery expression will make some queries better performing. This is why it is best to use the adjustment tools in the development phase.

To bind a dynamic variable to an XQuery expression

Another technique that can significantly improve the performance of an XQuery expression is to use a binding dynamic variable. Using bound variables instead of concatenating variables into strings enables Oracle to reuse SQL statements, reducing analysis overhead and significantly improving application performance. You can use the passing clause in Xmlquery and XMLTable SQL functions to bind dynamic variables to an XQuery expression. This technique enables you to dynamically generate XML based on parameters that are evaluated in client code. The example in Listing 3 shows how to use a binding variable in an XQuery query executed from a PHP script.

Listing 3: Using bound variables

!--? php
//file:bindvars.php
$user = ' hr ';
$pswd = ' hr ';
$db = ' (description=
(address_list=
address= (protocol=tcp) (Host=localhost) (port=1521)
)
( Connect_data= (SID=ORCLR2) (server=dedicated))
) ';
$empno = 100;
$conn = Oci_connect ($user, $PSWD, $db);
$sql = ' Select Xmlquery '. '. ' For $i in Ora:view ("Employees")/row the
where $i/employee_id = $empno
Return (
{$i/employee_id,
$i/email,
$i/job_id}
) '. '. ' Passing XmlElement ("Empno",: empno) as "Empno"
returning CONTENT). Getstringval () as result from DUAL ';
$query = Oci_parse ($conn, $sql);
Oci_bind_by_name ($query, ": Empno", $empno, 3);
Oci_execute ($query);
Oci_fetch ($query);
$str = Oci_result ($query, ' result ');
Print $str;
?>

The script shown in Listing 3 should produce the following output (note that the markup may not appear in the browser):

100
Sking
Ad_pres

XQuery and XSLT

Although Oracle provides a self-contained XSLT processor in Oracle XML DB, in many cases (especially when working with large documents), XQuery is more efficient at building XML. In addition, XQuery expressions are generally more readable and clearer than XSLT style sheets designed for the same job. Like XSLT, XQuery can be used not only to convert an XML document into another XML document, but also to convert XML to another text-based format, such as HTML or WML.

In the Query XmlType data section earlier in this article, you saw an example of using XQuery to convert an XML document to another XML document. Specifically, the example uses an XQuery expression to calculate the order totals for orders stored in the PurchaseOrder table of the sample database schema OE, and then generates a OrderTotal XML element for each order that is processed. In fact, you can use XSLT to perform the same operation. To do this, you first need to create an XSLT style sheet that applies to the PurchaseOrder XML document to produce the corresponding OrderTotal element. For this example, you can use the XSLT style sheet shown in Listing 4.

Listing 4: Using XSLT to compute subtotal totals (Quantity * UnitPrice)
-->
-->


























=2] ">

For convenience, you may want to save this XSL stylesheet in the database before you start using it. For example, you can save a style sheet as a file resource in an Oracle XML DB repository. One way to do this is to save the style sheet as a file to the local file system, and then use one of the following Internet protocols to move it to an XML repository: FTP, HTTP, or WebDAV. Suppose you have saved the XSLT style sheet in Listing 4 as ordertotal.xsl in the/public repository folder, and you can now use it as a parameter to the Xmltransform SQL function as shown in the following example (assuming you are logged in as Oe/oe):

SELECT Xmltransform (Object_value,
Xdburitype ('/public/ordertotal.xsl '). GetXML ()). Getstringval () as result from
PurchaseOrder WHERE Existsnode (object_value, '/purchaseorder[user = ' eabel '] ') = 1;

The above query processes all orders that the user Eabel requests (that is, orders that are stored in the default PurchaseOrder table in XmlType) and produces the same output as an XQuery query in the XmlType data section of the query.

Comparing the OrderTotal XSLT style sheet in Listing 4 with the XQuery expression used in the sample in the query XmlType data section, you may notice that the XQuery method is more appealing than the XSLT method. At least when using XQuery, you can get the same end result by writing very little code.

Query RSS News Offers

Because RSS feeds are essentially a managed XML file (RSS feeds get headlines or other content), you can handle it like any other XML document that can be obtained from the WEB. As you see in the Query external Data Sources section earlier in this article, you can use XQuery to query any XML that can be accessed through a URL. You dynamically bind all external XML data sources through the passing clause in XMLTable and xmlquery SQL functions. The following is an example of an XQuery provided by a query RSS news:

SELECT Xmlquery (
' For $i in $h//channel
Return


{$i/lastbuilddate}

{for $j in $h//item
where Ora:contains ($j, "PHP")
Return {($j/title, $j/link)}}

'
Passing Xmlparse (document Httpuritype
(' Http://www.oracle.com/technology/syndication/rss_otn_news.xml '). Getclob ()) as "H"
Returning CONTENT). Getstringval () as result from DUAL;

The XQuery should generate an XML document that contains a list of the most recently published headlines for PHP Technology from Oracle Technology Network (OTN). The resulting XML document might look like the following:

Tue, Nov 19:37:42 GMT



Http://www.oracle.com/technology/xe



Http://www.oracle.com/technology/pub/articles/oracle_php_cookbook



Http://www.oracle.com/technology/tech/php/zendcore/index.html

But when you develop a real application, you will most likely need an XQuery expression to generate HTML markup directly, rather than just generating an XML document as shown above. This allows you to build a more flexible, maintainable application, because in this case, all RSS processing, from extracting the necessary data to wrapping it in an HTML tag, will be transferred to the database. This makes it unnecessary for you to write application code that is responsible for RSS processing. In fact, this means that you do not have to modify the application code if the structure that is provided by the RSS news has changed. Instead, you just need to modify an XQuery expression for RSS processing.

Summarize

As you've learned in this article, XQuery is a comprehensive query language that provides an efficient way to query, build, and transform XML data. Although Oracle XQuery implementations allow you to manipulate any data that can be represented in XML (whether it is stored in a database, on a Web site, or in a file system), it is always a good idea to move the processed data to a database. For data stored in the database, Oracle XML DB (using the same mechanism for XPath overrides) can only significantly optimize the XQuery expressions built on the following data: This data includes relational data, object-relational data, or storage using structured (object-relational) storage technology based on XmlType data for XML schemas.