Oracle生成XML文件

最近在研究Oracle PLSQL中對於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;

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

二 如何存儲XML文件內容：三 如何解析XML內容：四 XMLTABLE用法：

to be continue...
---------------------------------- By Dylan.