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;

Oracle解析XML数据 

Oracle中使用存储过程解析XML字符串

Oracle EBS如何通过命令上传XML/BI Publisher数据定义文件和模板文件

Oracle Jdeveloper 上传XML文件到动态数据表中

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;

--XMLElement: 将一个关系值转换为XML元素的函数，格式为<elementName>值</elementName>

--XMLAttributes: 用于在SQL查询返回的 XML 元素中设置属性的函数

--XMLForest:      该函数返回一个或多个子元素的集合,该函数使用列名做为XML元素的名称并用SQL值表达式做为XML元素的内容，但使用时不能指定元素的属性

--XMLAgg:          在GROUP BY查询中对XML数据进行分组或汇总的函数

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

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