Oracle操作XML各種情境介紹

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;

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

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 3 4 下一頁