Oracle操作XML各种场景介绍

---转自http://blog.csdn.net/indexman/article/details/28130961

一 如何生成XML文件：

1、使用dbms_xmlquery和utl_file内置包（scott用户执行）

[sql]  view plain copy

1. CREATE OR REPLACE DIRECTORY xml_dir AS 'd:\app\xml';  
2.   
3. DROP SEQUENCE seq_filename;  
4. CREATE SEQUENCE seq_filename   
5.     MINVALUE 10000  
6.     MAXVALUE 99999   
7.     INCREMENT BY 1   
8.     START WITH 10000   
9.     NOCYCLE;  

[sql]  view plain copy

1. DECLARE  
2.     v_filename  Varchar2(50)  := 'Empmsg'||to_char(seq_filename.nextval)||'.xml';  
3.     xml_str     clob;  
4.     xml_file    utl_file.file_type;  
5.     offset      number;  
6.     buffer      varchar2(32767);  
7.     buffer_size number;  
8. BEGIN  
9.     offset      := 1;  
10.     buffer_size := 3000;  
11.     xml_file := utl_file.fopen('XML_DIR', v_filename, 'w');  
12.     xml_str  := dbms_xmlquery.getxml('select empno,  
13.                                              ename,  
14.                                              job,  
15.                                              mgr,  
16.                                              hiredate,  
17.                                              sal,  
18.                                              comm,  
19.                                              deptno  
20.                                       from emp');  
21.     
22.     while (offset < dbms_lob.getlength(xml_str)) loop  
23.       buffer := dbms_lob.substr(xml_str, buffer_size, offset);  
24.       utl_file.put(xml_file, buffer);  
25.       utl_file.fflush(xml_file);  
26.       offset := offset + buffer_size;  
27.     end loop;  
28.       
29.     utl_file.fclose(xml_file);  
30. END;  

        2、使用XMLELEMENT系列内置函数返回xml（sys用户执行）

[sql]  view plain copy

1. DECLARE  
2.     v_filename  Varchar2(50)  := 'Empmsg'||to_char(scott.seq_filename.nextval)||'.xml';  
3.     xml_str     clob;  
4.     xml_file    utl_file.file_type;  
5.     offset      number;  
6.     buffer      varchar2(32767);  
7.     buffer_size number;  
8. BEGIN  
9.     offset      := 1;  
10.     buffer_size := 3000;  
11.     xml_file := utl_file.fopen('XML_DIR', v_filename, 'w');  
12.     SELECT XMLElement("DEPARTMENT"  
13.                  , XMLAttributes( department_id as "ID"  
14.                                 , department_name as "NAME"  
15.                                 )  
16.                  , XMLElement("EMPLOYEES"  
17.                              , (SELECT XMLAgg( XMLElement("EMPLOYEE"  
18.                                                          , XMLForest(employee_id as "ID"  
19.                                                                     ,first_name||' '||last_name as "NAME"  
20.                                                                     )  
21.                                                          )  
22.                                              )  
23.                                  FROM hr.employees emp  
24.                                 WHERE emp.department_id = dept.department_id  
25.                                 )  
26.                               )  
27.                 ).getclobval() INTO xml_str  
28.      FROM hr.departments dept  
29.      WHERE department_id = 20;  
30.     
31.     while (offset < dbms_lob.getlength(xml_str)) loop  
32.       buffer := dbms_lob.substr(xml_str, buffer_size, offset);  
33.       utl_file.put(xml_file, buffer);  
34.       utl_file.fflush(xml_file);  
35.       offset := offset + buffer_size;  
36.     end loop;  
37.       
38.     utl_file.fclose(xml_file);  
39. END;  

--XMLElement: 将一个关系值转换为XML元素的函数，格式为<elementName>值</elementName> 
--XMLAttributes: 用于在SQL查询返回的 XML 元素中设置属性的函数 
--XMLForest:      该函数返回一个或多个子元素的集合,该函数使用列名做为XML元素的名称并用SQL值表达式做为XML元素的内容，但使用时不能指定元素的属性 
--XMLAgg:           在GROUP BY查询中对XML数据进行分组或汇总的函数 

PS: 使用SPOOL方式导出文件：

[sql]  view plain copy

1. SET TRIMSPOOL ON   
2. SET TERMOUT ON   
3. SET FEEDBACK OFF   
4. SET VERIFY OFF   
5. SET ECHO OFF   
6. SET PAGESIZE 999   
7. SET HEAD OFF   
8. SET HEADING OFF   
9. SET LONG 5000  
10. spool c:\a.xml  
11. SELECT XMLElement("DEPARTMENT"  
12.                  , XMLAttributes( department_id as "ID"  
13.                                 , department_name as "NAME"  
14.                                 )  
15.                  , XMLElement("EMPLOYEES"  
16.                              , (SELECT XMLAgg( XMLElement("EMPLOYEE"  
17.                                                          , XMLForest(employee_id as "ID"  
18.                                                                     ,first_name||' '||last_name as "NAME"  
19.                                                                     )  
20.                                                          )  
21.                                              )  
22.                                  FROM employees emp  
23.                                 WHERE emp.department_id = dept.department_id  
24.                                 )  
25.                               )  
26.                 ) a  
27.   FROM departments dept  
28.  WHERE department_id = 10;  
29. spool off  

二 如何存储XML文件内容：

我们知道oracle 中xmltype数据类型用来存储XML内容。下面例子中介绍如何将系统中XML文件内容加载至

含有XMLTYPE类型的表中。

[sql]  view plain copy

1. CREATE TABLE xml_table OF XMLTYPE;  
2.   
3. INSERT INTO xml_table   
4.     VALUES(XMLTYPE(bfilename('XML_DIR','PurchaseOrder.xml'),nls_charset_id('AL32UTF8')));  
5.       
6.     SELECT x.sys_nc_rowinfo$.getstringval() FROM xml_table x;  
7.       
8. CREATE TABLE table_with_xml_column(filename VARCHAR2(64), xml_document XMLTYPE);  
9.   
10. INSERT INTO table_with_xml_column  
11.     VALUES ('PurchaseOrder.xml',XMLType(bfilename('XML_DIR', 'PurchaseOrder.xml'),nls_charset_id('AL32UTF8')));  
12.       
13. SELECT x.xml_document.getCLOBVal() FROM table_with_xml_column x;  

PurchaseOrder.xml内容：

[html]  view plain copy

1. <PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
2.   xsi:noNamespaceSchemaLocation=  
3.     "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">  
4.   <Reference>SBELL-2002100912333601PDT</Reference>  
5.   <Actions>  
6.     <Action>  
7.       <User>SVOLLMAN</User>  
8.     </Action>  
9.   </Actions>  
10.   <Reject/>  
11.   <Requestor>Sarah J. Bell</Requestor>  
12.   <User>SBELL</User>  
13.   <CostCenter>S30</CostCenter>  
14.   <ShippingInstructions>  
15.     <name>Sarah J. Bell</name>  
16.     <address>400 Oracle Parkway  
17.       Redwood Shores  
18.       CA  
19.       94065  
20.       USA</address>  
21.     <telephone>650 506 7400</telephone>  
22.   </ShippingInstructions>  
23.   <SpecialInstructions>Air Mail</SpecialInstructions>  
24.   <LineItems>  
25.     <LineItem ItemNumber="1">  
26.       <Description>A Night to Remember</Description>  
27.       <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>  
28.     </LineItem>  
29.     <LineItem ItemNumber="2">  
30.       <Description>The Unbearable Lightness Of Being</Description>  
31.       <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>  
32.     </LineItem>  
33.     <LineItem ItemNumber="3">  
34.       <Description>Sisters</Description>  
35.       <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>  
36.     </LineItem>  
37.   </LineItems>  
38. </PurchaseOrder>  

三 如何解析XML内容：

 1、XPath结构介绍：

    

         /       表示树根。例如：/PO 指向树根的子节点<PO>; 通常也作为路径分隔符使用，例如：/A/B

        //  表示当前节点下所有子节点；例如：/A//B 匹配A节点下所有B节点

        *       作为通配符使用，匹配所有子节点； 如：/A/*/C 匹配A节点下所有子节点C

        [ ]      表示预期表达式；XPath支持丰富的操作符如OR、AND、NOT等；例如：/PO[PONO=20 AND PNAME="PO_2"]/SHIPADDR 匹配所有订单号为20并且订单名为PO_2的送货地址

@   用来提取节点属性

        Functions XPath支持一些内置函数如：substring(), round() 和 not(). 

      2、使用XMLTYPE方法查询XML：

            SELECT x.OBJECT_VALUE. getCLOBVal() FROM xml_table x;

             SELECT x.OBJECT_VALUE. getSTRINGVal() FROM xml_table x;

      3、使用函数解析XML：

--existsNode 判断XPath中节点是否存在，存在返回值1 不存在返回0；

[sql]  view plain copy

1. SELECT existsNode(OBJECT_VALUE, '/PurchaseOrder/Reference')  
2. FROM purchaseorder;  

--extractValue  提取XPath节点值

[sql]  view plain copy

1. SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference')  
2. FROM purchaseorder  
3. WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/Reference') = 1;  

--extract   提取XPath节点

[sql]  view plain copy

1. SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Reference') "REFERENCE"  
2. FROM purchaseorder;  

4、使用SQL解析XML：

[sql]  view plain copy

1. SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') REFERENCE,  
2.     extractValue(OBJECT_VALUE, '/PurchaseOrder/*//User') USERID,  
3.     CASE  
4.         WHEN existsNode(OBJECT_VALUE, '/PurchaseOrder/Reject') = 1  
5.         THEN 'Rejected'  
6.         ELSE 'Accepted'  
7.     END "STATUS",  
8. extractValue(OBJECT_VALUE, '//CostCenter') CostCenter  
9. FROM purchaseorder  
10. WHERE existsNode(OBJECT_VALUE,'//Reject') = 1;  

四   XMLTABLE用法：

XMLTable maps the result of an XQuery evaluation into relational rows and columns. You can query the result returned by the function as a virtual relational table using SQL.

说白了就是解析XML内容返回虚拟关系型结构数据。 下面说个简单例子：

        

        

[sql]  view plain copy

1. CREATE TABLE warehouses(  
2.     warehouse_id NUMBER(3),  
3.     warehouse_spec SYS.XMLTYPE,  
4.     warehouse_name VARCHAR2(35),  
5. location_id NUMBER(4)  
6. );  

[sql]  view plain copy

1. INSERT into warehouses (warehouse_id, warehouse_spec,warehouse_name) VALUES (100, sys.XMLType.createXML(  
2.   
3. '<Warehouse whNo="100">  
4.    <opt1>  
5.         <Building>Owned</Building>  
6.         <WaterAccess>WaterAccess</WaterAccess>  
7.         <RailAccess>RailAccess</RailAccess>  
8.         <field>f1</field>  
9.         <field>f2</field>  
10.         <field>f3</field>  
11.    </opt1>  
12.    <opt2>  
13.         <name>Dylan</name>  
14.    </opt2>  
15. </Warehouse>'),'Warehouse-X');      

[sql]  view plain copy

1. SELECT warehouse_name warehouse,  
2.    warehouse2."whNo"  
3. FROM warehouses,  
4.    XMLTABLE('/Warehouse'  
5.       PASSING warehouses.warehouse_spec  
6.       COLUMNS   
7.          "whNo" varchar2(100) PATH '@whNo')  
8.       warehouse2;  

[sql]  view plain copy

1. SELECT warehouse_name warehouse,  
2.    warehouse2."Water", warehouse2."Rail", warehouse2.field  
3.    FROM warehouses,  
4.    XMLTABLE('*//opt1'  
5.       PASSING warehouses.warehouse_spec  
6.       COLUMNS   
7.          "Water" varchar2(100) PATH '//WaterAccess',  
8.          "Rail" varchar2(100) PATH '//RailAccess',  
9.          field XMLTYPE PATH '/')   
10.       warehouse2;  
11.