Oracle XQuery query, build, and convert XML 1

Oracle XQuery query, build, and convert XML (1)

In Oracle databases 10 G In version 2nd, Oracle introduces a fully functional built-in XQuery Engine Integrated with the database, which can be used to complete various tasks related to the development of XML-Supported Applications. XQuery is a query language used to process XML data models. It can operate on any type of data that can be expressed in XML. Although Oracle XQuery implementation allows you to use database data and external data sources, Oracle xml db typically significantly improves performance in terms of processing structured data stored in the database.

The examples provided in this article demonstrate not only the situations where and how to use XQuery to query, construct, and convert XML, but also how to monitor and analyze the performance execution of XQuery expressions, to find a more efficient way to handle the same workload.

Build Based on Relational DataXML

When necessary (for example, sending results to Web Services), you may need to build XML based on relational data. To go to Oracle Database 10GTo complete this task in versions earlier than version 2nd, you usually need to use SQL/XML to generate functions, such as xmlelement, xmlforest, and xmldetail (). In Oracle Database 10GIn version 2nd, XQuery will be more efficient than these functions. Specifically, using the ora: View XQuery function within an XQuery expression, you can query existing Relational Tables or views and build XML in real time without explicitly creating an XML view through relational data. The PL/SQL code in List 1 demonstrates how to use ora: View to build an XML document based on the data stored in the default employee relationship table in the example database mode HR.

List1: UseOra: ViewCreate Based on Relational DataXML

Begin If (dbms_xdb.createfolder ('/public/employees') then Dbms_output.put_line ('Folder is created '); Else Dbms_output.put_line ('could not 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/FIG <= 102 Return ( {Xs: string ($ I/employee_id )} {Xs: string ($ I/last_name )} {Xs: INTEGER ($ I/salary )} )})' Returning content) into xmldoc from dual; If (dbms_xdb.createresource ('/public/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 PROCESS IN LIST 1, you only created a new folder in the XML Information Library. In the Information Library folder, you will then store the XML document created during the second PL/SQL process shown here. The second PL/SQL process first issues a SELECT statement, which uses the xmlquery SQL function to build XML based on relational data. For an XQuery expression (xmlquery uses it as a parameter here), pay attention to the ora: View XQuery function 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 in the HR database mode. Therefore, ora: View returns an employee XML document sequence that represents the row of the HR. Employees table. However, to save space in the Results document, only the first three employee records are passed to the result sequence. This is achieved by specifying $ I/employee_id <= 102 in the WHERE clause of the flwor expression. Note the Xs: string () and Xs: INTEGER () XQuery expressions used in the return clause of the flwor expression. In fact, the two XQuery expressions used here not only convert the XML node value to the corresponding type, but also extract these node values. Then, the generated employee XML document is saved as employees. XML to the/public/employees XML Information Library folder created in the PL/SQL PROCESS IN LIST 1. To ensure that this operation has been completed, perform the following query:

Select xmlquery ('for $ I In FN: DOC ("/public/employees. xml ") Return; $ I' Returning content) as result from dual;

The following output is generated for this query:

100 King 24000 101 Kochhar 17000 102 De Haan 17000

In the preceding XQuery, the FN: Doc XQuery function is used to access a single XML document stored in the Oracle xml db database. But what should I do if I want to process some XML documents with the same or similar structure (stored in the same XML library folder? In this case, another XQuery function (FN: Collection) used to process XML database resources may come in handy. This article will introduce several examples of how to use the FN: Collection XQuery function later.

QueryXmltypeData

XQuery allows you to operate on XML-based and non-schema-based data. The following example demonstrates how to use the xmltable function to query an xmltype table in the purchaseorder XML mode from the OE demo database mode.

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 the context item to the XQuery expression used here. The XQuery expression calculates the total amount of each purchase order requested by the user eabel, and generates an ordertotal XML Element for each order processed. To access the generated XML, use the column_value virtual column in the select list. The final output should be as follows:

Ordertotal ------------------------------------------------------------- EABEL-20021009123338324PDT 1328.05 EABEL-20021009123335791PDT 2067.15 EABEL-20021009123336251PDT 289.6 EABEL-20021009123336382PDT 928.92

To obtain the same final result, you can use the xmlquery function instead. However, if you pass the XQuery expression parameters used in the previous example to xmlquery (as shown 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 connected to the purchaseorder table, which is included in the query summary result set. In fact, this means that the output will not only contain the ordertotal element generated for the order requested by the user eabel, but also contain empty rows generated for all other orders stored in the purchaseorder table (by default, the purchaseorder table contains 132 rows ). 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, instead of using the WHERE clause in the XQuery expression, as shown below:

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.

 

Oracle XQuery query, build, and convert XML (2)

 

QueryOracle XML DBIn the Information LibraryXMLData

To access the XML data stored in the Oracle xml db database, Oracle XQuery introduces the FN: Doc and FN: Collection XQuery functions. Using FN: Doc, you can query a single XML document stored in the XML Information Library, while FN: Collection allows you to access multiple XML documents stored in the same information library folder.

As demonstrated in the examples described earlier in this article (see section on Building XML using relational data), using FN: Doc is very simple and straightforward. It obtains the string representing the library file resource (URI) and returns the document pointed to by the URI. To understand the function of FN: Collection XQuery, there must be at least two library files in the same folder. If you have run the code in List 1, you have created the/public/employees information library folder and stored the employees. xml file. Therefore, you need to create at least one XML file in the folder before you can try FN: collection. The PL/SQL code in List 2 is based on Scott/tiger to demonstrate how to build XML based on the relational data stored in the dept and EMP tables in the database mode, save the generated XML document as acc_dept.xml to the/public/employees information library folder. To run the PL/SQL process in List 2, make sure to log on as Scott/tiger.

List2: Build based on Relational DataXMLAnd save itXMLInformation Library

Declare Xmldoc xmltype; Begin Select xmlquery ( 'For $ J in ora: View ("Scott", "Dept")/row Where $ J/deptno = 10 Return ( {$ J/deptno, $ J/dname} { For $ I in ora: View ("Scott", "EMP")/row Where $ I/deptno = $ J/deptno Return ( {$ I/empno, $ I/ename, $ I/Sal} )} )' Returning content) into 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; /

In this case, the/public/employees information library folder should contain two files: acc_dept.xml (generated by PL/SQL code in List 2) and employees. XML (generated by code in List 1 ). Because these XML documents are stored in the same information library folder, you can use the FN: collection function to access the employee information stored in the two XML documents. However, although these XML documents contain employee XML elements (these elements actually have the same structure), the structure of the XML document itself is quite different. In employees. XML, the root element of the document is employees, while acc_dept.xml uses Department as the root element. To solve this problem, you can use XQuery to use XPath // to navigate to a node in the XML document without specifying the exact path of the node. The following example demonstrates how to use XPath // to construct an XQuery expression:

Select xmlquery ( 'For $ I In FN: Collection ("/public/employees") // employee Where $ I/Sal & gt; = 5000 Order by $ I/ename Return; $ I' Returning content) from dual;

This structure should generate the following output:

102 De Haan 17000 7839 King 5000 100 King 24000 101 Kochhar 17000

As you can see, the above output contains the employee XML elements obtained from employees. xml and acc_dept.xml, which indicate employees with a salary of more than or equal to $5,000.

SetXMLDecomposed into relational data

If the application processes relational data rather than XML, and the data you want to access is stored in XML format, it may be very useful to break down XML into relational data. In the previous example, you can use the SQL function xmltable to split the employee XML element into a single column of the virtual table, as shown below:

Select EMPs. empno, EMPs. ename, EMPs. Sal from Xmltable ( 'For $ I In FN: Collection ("/public/employees") // employee Where $ I/Sal & gt; = 5000 Return; $ I' Columns empno number path'/employee/empno ', Ename varchar2 (30) path '/employee/ename ', Sal number path '/employee/Sal') EMPs;

The query generates the following output:

Empno ename Sal ----------------------------- 7839 King 5000 100 king 24000 101 Kochhar 17000 102 de Haan 17000