Oracle操作XML各种场景介绍

来源：互联网发布：好看网络剧推荐编辑：程序博客网时间：2024/05/21 03:27

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

一如何生成XML文件：

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

[sql] view plaincopy
CREATE OR REPLACE DIRECTORY xml_dir AS 'd:\app\xml';  
  
DROP SEQUENCE seq_filename;  
CREATE SEQUENCE seq_filename   
    MINVALUE 10000  
    MAXVALUE 99999   
    INCREMENT BY 1   
    START WITH 10000   
    NOCYCLE;  

[sql] view plaincopy
DECLARE  
    v_filename  Varchar2(50)  := 'Empmsg'||to_char(seq_filename.nextval)||'.xml';  
    xml_str     clob;  
    xml_file    utl_file.file_type;  
    offset      number;  
    buffer      varchar2(32767);  
    buffer_size number;  
BEGIN  
    offset      := 1;  
    buffer_size := 3000;  
    xml_file := utl_file.fopen('XML_DIR', v_filename, 'w');  
    xml_str  := dbms_xmlquery.getxml('select empno,  
                                             ename,  
                                             job,  
                                             mgr,  
                                             hiredate,  
                                             sal,  
                                             comm,  
                                             deptno  
                                      from emp');  
    
    while (offset < dbms_lob.getlength(xml_str)) loop  
      buffer := dbms_lob.substr(xml_str, buffer_size, offset);  
      utl_file.put(xml_file, buffer);  
      utl_file.fflush(xml_file);  
      offset := offset + buffer_size;  
    end loop;  
      
    utl_file.fclose(xml_file);  
END;  

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

[sql] view plaincopy
DECLARE  
    v_filename  Varchar2(50)  := 'Empmsg'||to_char(scott.seq_filename.nextval)||'.xml';  
    xml_str     clob;  
    xml_file    utl_file.file_type;  
    offset      number;  
    buffer      varchar2(32767);  
    buffer_size number;  
BEGIN  
    offset      := 1;  
    buffer_size := 3000;  
    xml_file := utl_file.fopen('XML_DIR', v_filename, 'w');  
    SELECT XMLElement("DEPARTMENT"  
                 , XMLAttributes( department_id as "ID"  
                                , department_name as "NAME"  
                                )  
                 , XMLElement("EMPLOYEES"  
                             , (SELECT XMLAgg( XMLElement("EMPLOYEE"  
                                                         , XMLForest(employee_id as "ID"  
                                                                    ,first_name||' '||last_name as "NAME"  
                                                                    )  
                                                         )  
                                             )  
                                 FROM hr.employees emp  
                                WHERE emp.department_id = dept.department_id  
                                )  
                              )  
                ).getclobval() INTO xml_str  
     FROM hr.departments dept  
     WHERE department_id = 20;  
    
    while (offset < dbms_lob.getlength(xml_str)) loop  
      buffer := dbms_lob.substr(xml_str, buffer_size, offset);  
      utl_file.put(xml_file, buffer);  
      utl_file.fflush(xml_file);  
      offset := offset + buffer_size;  
    end loop;  
      
    utl_file.fclose(xml_file);  
END;  

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

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

[sql] view plaincopy
SET TRIMSPOOL ON   
SET TERMOUT ON   
SET FEEDBACK OFF   
SET VERIFY OFF   
SET ECHO OFF   
SET PAGESIZE 999   
SET HEAD OFF   
SET HEADING OFF   
SET LONG 5000  
spool c:\a.xml  
SELECT XMLElement("DEPARTMENT"  
                 , XMLAttributes( department_id as "ID"  
                                , department_name as "NAME"  
                                )  
                 , XMLElement("EMPLOYEES"  
                             , (SELECT XMLAgg( XMLElement("EMPLOYEE"  
                                                         , XMLForest(employee_id as "ID"  
                                                                    ,first_name||' '||last_name as "NAME"  
                                                                    )  
                                                         )  
                                             )  
                                 FROM employees emp  
                                WHERE emp.department_id = dept.department_id  
                                )  
                              )  
                ) a  
  FROM departments dept  
 WHERE department_id = 10;  
spool off  

二如何存储XML文件内容：

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

含有XMLTYPE类型的表中。

[sql] view plaincopy
CREATE TABLE xml_table OF XMLTYPE;  
  
INSERT INTO xml_table   
    VALUES(XMLTYPE(bfilename('XML_DIR','PurchaseOrder.xml'),nls_charset_id('AL32UTF8')));  
      
    SELECT x.sys_nc_rowinfo$.getstringval() FROM xml_table x;  
      
CREATE TABLE table_with_xml_column(filename VARCHAR2(64), xml_document XMLTYPE);  
  
INSERT INTO table_with_xml_column  
    VALUES ('PurchaseOrder.xml',XMLType(bfilename('XML_DIR', 'PurchaseOrder.xml'),nls_charset_id('AL32UTF8')));  
      
SELECT x.xml_document.getCLOBVal() FROM table_with_xml_column x;  

PurchaseOrder.xml内容：

[html] view plaincopy
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  xsi:noNamespaceSchemaLocation=  
    "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">  
  <Reference>SBELL-2002100912333601PDT</Reference>  
  <Actions>  
    <Action>  
      <User>SVOLLMAN</User>  
    </Action>  
  </Actions>  
  <Reject/>  
  <Requestor>Sarah J. Bell</Requestor>  
  <User>SBELL</User>  
  <CostCenter>S30</CostCenter>  
  <ShippingInstructions>  
    <name>Sarah J. Bell</name>  
    <address>400 Oracle Parkway  
      Redwood Shores  
      CA  
      94065  
      USA</address>  
    <telephone>650 506 7400</telephone>  
  </ShippingInstructions>  
  <SpecialInstructions>Air Mail</SpecialInstructions>  
  <LineItems>  
    <LineItem ItemNumber="1">  
      <Description>A Night to Remember</Description>  
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>  
    </LineItem>  
    <LineItem ItemNumber="2">  
      <Description>The Unbearable Lightness Of Being</Description>  
      <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>  
    </LineItem>  
    <LineItem ItemNumber="3">  
      <Description>Sisters</Description>  
      <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>  
    </LineItem>  
  </LineItems>  
</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的送货地址

@ 用来提取节点属性

FunctionsXPath支持一些内置函数如：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 plaincopy
SELECT existsNode(OBJECT_VALUE, '/PurchaseOrder/Reference')  
FROM purchaseorder;  

--extractValue 提取XPath节点值

[sql] view plaincopy
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference')  
FROM purchaseorder  
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/Reference') = 1;  

--extract 提取XPath节点

[sql] view plaincopy
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Reference') "REFERENCE"  
FROM purchaseorder;  

4、使用SQL解析XML：

[sql] view plaincopy
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') REFERENCE,  
    extractValue(OBJECT_VALUE, '/PurchaseOrder/*//User') USERID,  
    CASE  
        WHEN existsNode(OBJECT_VALUE, '/PurchaseOrder/Reject') = 1  
        THEN 'Rejected'  
        ELSE 'Accepted'  
    END "STATUS",  
extractValue(OBJECT_VALUE, '//CostCenter') CostCenter  
FROM purchaseorder  
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 plaincopy
CREATE TABLE warehouses(  
    warehouse_id NUMBER(3),  
    warehouse_spec SYS.XMLTYPE,  
    warehouse_name VARCHAR2(35),  
location_id NUMBER(4)  
);  

[sql] view plaincopy
INSERT into warehouses (warehouse_id, warehouse_spec,warehouse_name) VALUES (100, sys.XMLType.createXML(  
  
'<Warehouse whNo="100">  
   <opt1>  
        <Building>Owned</Building>  
        <WaterAccess>WaterAccess</WaterAccess>  
        <RailAccess>RailAccess</RailAccess>  
        <field>f1</field>  
        <field>f2</field>  
        <field>f3</field>  
   </opt1>  
   <opt2>  
        <name>Dylan</name>  
   </opt2>  
</Warehouse>'),'Warehouse-X');      

[sql] view plaincopy
SELECT warehouse_name warehouse,  
   warehouse2."whNo"  
FROM warehouses,  
   XMLTABLE('/Warehouse'  
      PASSING warehouses.warehouse_spec  
      COLUMNS   
         "whNo" varchar2(100) PATH '@whNo')  
      warehouse2;  

[sql] view plaincopy
SELECT warehouse_name warehouse,  
   warehouse2."Water", warehouse2."Rail", warehouse2.field  
   FROM warehouses,  
   XMLTABLE('*//opt1'  
      PASSING warehouses.warehouse_spec  
      COLUMNS   
         "Water" varchar2(100) PATH '//WaterAccess',  
         "Rail" varchar2(100) PATH '//RailAccess',  
         field XMLTYPE PATH '/')   
      warehouse2;  

0 0