Oracle XML 處理實戰：PL/SQL 生成 XML 文檔與解析 XML 入表

Oracle 內建 XMLDOM、XMLPARSER 套件提供 W3C DOM 標準介面，可在 PL/SQL 中程式化生成、解析 XML。本文提供兩個完整範例：存儲過程生成專案 XML、存儲過程解析 XML 入表，涵蓋 DOM 節點操作、游標綁定、CLOB 處理、異常捕獲。

一、生成 XML：專案資料轉 XML 文檔

CREATE OR REPLACE PROCEDURE proj_xml (p_st_id VARCHAR2) IS
  v_row      pdpla_approve_proj%ROWTYPE;
  v_sql      VARCHAR2(200) := 'SELECT * FROM pdpla_approve_proj WHERE st_id = ''' || p_st_id || '''';
  TYPE cur_t IS REF CURSOR;
  v_cur      cur_t;
  v_doc      xmldom.DOMDocument := xmldom.newDOMDocument;
  v_doc_node xmldom.DOMNode := xmldom.makeNode(v_doc);
  v_root_el  xmldom.DOMElement := xmldom.createElement(v_doc, 'root');
  v_proj_el  xmldom.DOMElement;
  v_child_el xmldom.DOMElement;
  v_root_node xmldom.DOMNode;
  v_proj_node xmldom.DOMNode;
  v_text_node xmldom.DOMNode;
  v_buf      VARCHAR2(32767);
  v_cnt      NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('SQL: ' || v_sql);
  v_root_node := xmldom.appendChild(v_doc_node, xmldom.makeNode(v_root_el));
  xmldom.setAttribute(v_root_el, 'id', p_st_id);

  OPEN v_cur FOR v_sql;
  LOOP
    FETCH v_cur INTO v_row;
    EXIT WHEN v_cur%NOTFOUND;

    v_proj_el := xmldom.createElement(v_doc, 'project');
    v_proj_node := xmldom.appendChild(v_root_node, xmldom.makeNode(v_proj_el));

    -- 子節點 1: st_pro_id
    v_child_el := xmldom.createElement(v_doc, 'st_pro_id');
    v_text_node := xmldom.appendChild(xmldom.makeNode(v_child_el),
                        xmldom.makeNode(xmldom.createTextNode(v_doc, v_row.st_project_id)));
    xmldom.appendChild(v_proj_node, xmldom.makeNode(v_child_el));

    -- 子節點 2: st_info_type
    v_child_el := xmldom.createElement(v_doc, 'st_info_type');
    v_text_node := xmldom.appendChild(xmldom.makeNode(v_child_el),
                        xmldom.makeNode(xmldom.createTextNode(v_doc, v_row.st_info_type)));
    xmldom.appendChild(v_proj_node, xmldom.makeNode(v_child_el));

    v_cnt := v_cnt + 1;
  END LOOP;
  CLOSE v_cur;

  xmldom.writeToBuffer(v_doc, v_buf);
  xmldom.freeDocument(v_doc);

  DBMS_OUTPUT.PUT_LINE('Count: ' || v_cnt);
  DBMS_OUTPUT.PUT_LINE('XML: ' || v_buf);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    IF v_doc IS NOT NULL THEN xmldom.freeDocument(v_doc); END IF;
END;
/

二、解析 XML：CLOB 型 XML 字串拆解入表

CREATE OR REPLACE PROCEDURE p_parse (p_id VARCHAR2) IS
  v_parser   xmlparser.Parser := xmlparser.newParser;
  v_doc      xmldom.DOMDocument;
  v_items    xmldom.DOMNodeList;
  v_children xmldom.DOMNodeList;
  v_item     xmldom.DOMNode;
  v_attrs    xmldom.DOMNamedNodeMap;
  v_newid    NUMBER;
  v_name     VARCHAR2(100);
  v_value    VARCHAR2(100);
  v_xml      CLOB;
BEGIN
  FOR r IN (SELECT xml_data, id FROM xmldata WHERE id = p_id) LOOP
    v_xml := r.xml_data;

    v_parser := xmlparser.newParser;
    xmlparser.parseClob(v_parser, v_xml);
    v_doc := xmlparser.getDocument(v_parser);
    xmlparser.freeParser(v_parser);

    -- 取得所有  節點
    v_items := xmldom.getElementsByTagName(v_doc, 'DATA');
    FOR i IN 0 .. xmldom.getLength(v_items) - 1 LOOP
      BEGIN
        v_item := xmldom.item(v_items, i);
        v_children := xmldom.getChildNodes(v_item);

        -- 假設子節點順序固定: newid, name, value
        v_newid := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(v_children, 0)));
        v_name  := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(v_children, 1)));
        v_value := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(v_children, 2)));

        INSERT INTO xmltest VALUES (v_newid, v_name, v_value);
        COMMIT;
      EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Parse error item '||i||': '||SQLERRM);
      END;
    END LOOP;

    xmldom.freeDocument(v_doc);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Fatal: ' || SQLERRM);
    IF v_doc IS NOT NULL THEN xmldom.freeDocument(v_doc); END IF;
    IF v_parser IS NOT NULL THEN xmlparser.freeParser(v_parser); END IF;
END;
/

三、關鍵 API 速查表

四、效能與最佳實踐

• 大量資料改用 XMLTYPE：10g+ XMLTYPE 物件型別支援 XQuery、XPath、索引 (XMLIndex)，效能遠優於 DOM 逐節點操作。
• 批次提交：解析入表時累積 N 筆 (如 1000) 再 COMMIT，減少 Redo/Undo 開銷。
• 命名空間處理：若 XML 含命名空間，需用 getElementsByTagNameNS、setAttributeNS 等 NS 版 API。
• 記憶體管理：freeDocument/freeParser 必須在 EXCEPTION 區塊也執行，避免 PGA 洩漏。