Oracle操作XML各种场景介绍

一 怎样生成XML文件：

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

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;

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用户运行）

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;

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文件内容：

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 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>

SELECT existsNode(OBJECT_VALUE, ‘/PurchaseOrder/Reference‘)
FROM purchaseorder;

SELECT extractValue(OBJECT_VALUE, ‘/PurchaseOrder/Reference‘)
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, ‘/PurchaseOrder/Reference‘) = 1;

SELECT extract(OBJECT_VALUE, ‘/PurchaseOrder/Reference‘) "REFERENCE"
FROM purchaseorder;

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;

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

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‘);	

SELECT warehouse_name warehouse,
   warehouse2."whNo"
FROM warehouses,
   XMLTABLE(‘/Warehouse‘
      PASSING warehouses.warehouse_spec
      COLUMNS 
         "whNo" varchar2(100) PATH ‘@whNo‘)
      warehouse2;

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;