將表 PDPLA_APPROVE_PROJ 查詢結果生成 XML,及解析 XML 入庫。
CREATE OR REPLACE PROCEDURE PROJ_XML (ST_ID VARCHAR2) IS
PRO_ROWS PDPLA_APPROVE_PROJ%ROWTYPE;
TEMPSQL VARCHAR2(1000);
TYPE CUR IS REF CURSOR;
PROJ_CUR CUR;
DOC XMLDOM.DOMDOCUMENT := XMLDOM.NEWDOMDOCUMENT;
DOC_NODE XMLDOM.DOMNODE := XMLDOM.MAKENODE(DOC);
ROOT_ELE XMLDOM.DOMELEMENT := XMLDOM.CREATEELEMENT(DOC, 'ROOT');
ROOT_NODE XMLDOM.DOMNODE;
-- 子元素變數略
CNT NUMBER := 1;
BUF VARCHAR2(1000);
BEGIN
TEMPSQL := 'SELECT * FROM PDPLA_APPROVE_PROJ WHERE ST_ID = ''' || ST_ID || '''';
ROOT_NODE := XMLDOM.APPENDCHILD(DOC_NODE, XMLDOM.MAKENODE(ROOT_ELE));
XMLDOM.SETATTRIBUTE(ROOT_ELE, 'ID', ST_ID);
OPEN PROJ_CUR FOR TEMPSQL;
LOOP
FETCH PROJ_CUR INTO PRO_ROWS;
EXIT WHEN PROJ_CUR%NOTFOUND;
-- 建立 節點
DECLARE
PROJECT_ELE XMLDOM.DOMELEMENT := XMLDOM.CREATEELEMENT(DOC, 'PROJECT');
PROJECT_NODE XMLDOM.DOMNODE := XMLDOM.APPENDCHILD(ROOT_NODE, XMLDOM.MAKENODE(PROJECT_ELE));
ST_PRO_ELE XMLDOM.DOMELEMENT;
ST_PRO_NODE XMLDOM.DOMNODE;
T_NODE XMLDOM.DOMNODE;
BEGIN
ST_PRO_ELE := XMLDOM.CREATEELEMENT(DOC, 'ST_PRO_ID');
ST_PRO_NODE := XMLDOM.APPENDCHILD(PROJECT_NODE, XMLDOM.MAKENODE(ST_PRO_ELE));
T_NODE := XMLDOM.APPENDCHILD(ST_PRO_NODE, XMLDOM.MAKENODE(XMLDOM.CREATETEXTNODE(DOC, PRO_ROWS.ST_PROJECT_ID)));
ST_PRO_ELE := XMLDOM.CREATEELEMENT(DOC, 'ST_INFO_TYPE');
ST_PRO_NODE := XMLDOM.APPENDCHILD(PROJECT_NODE, XMLDOM.MAKENODE(ST_PRO_ELE));
T_NODE := XMLDOM.APPENDCHILD(ST_PRO_NODE, XMLDOM.MAKENODE(XMLDOM.CREATETEXTNODE(DOC, PRO_ROWS.ST_INFO_TYPE)));
END;
CNT := CNT + 1;
END LOOP;
CLOSE PROJ_CUR;
XMLDOM.WRITETOBUFFER(DOC, BUF);
XMLDOM.FREEDOCUMENT(DOC);
DBMS_OUTPUT.PUT_LINE('CNT:' || CNT);
DBMS_OUTPUT.PUT_LINE('XML:' || BUF);
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERR:' || SQLERRM);
END PROJ_XML;
CREATE OR REPLACE PROCEDURE P_PARSE (ID IN VARCHAR2) IS
XMLPAR XMLPARSER.PARSER := XMLPARSER.NEWPARSER;
XDOC XMLDOM.DOMDOCUMENT;
ITEMNODES XMLDOM.DOMNODELIST;
CHILDNODES XMLDOM.DOMNODELIST;
ITEMNODE XMLDOM.DOMNODE;
ITEMARRMAP XMLDOM.DOMNAMEDNODEMAP;
NEWID NUMBER; NAME VARCHAR2(100); VALUE VARCHAR2(100);
XMLSTR CLOB;
BEGIN
FOR CUR IN (SELECT XML_DATA, ID FROM XMLDATA WHERE ID = P_PARSE.ID) LOOP
XMLSTR := CUR.XML_DATA;
XMLPAR := XMLPARSER.NEWPARSER;
XMLPARSER.PARSECLOB(XMLPAR, XMLSTR);
XDOC := XMLPARSER.GETDOCUMENT(XMLPAR);
XMLPARSER.FREEPARSER(XMLPAR);
ITEMNODES := XMLDOM.GETELEMENTSBYTAGNAME(XDOC, 'DATA');
FOR I IN 0 .. XMLDOM.GETLENGTH(ITEMNODES)-1 LOOP
ITEMNODE := XMLDOM.ITEM(ITEMNODES, I);
CHILDNODES := XMLDOM.GETCHILDNODES(ITEMNODE);
NEWID := XMLDOM.GETNODEVALUE(XMLDOM.GETFIRSTCHILD(XMLDOM.ITEM(CHILDNODES, 0)));
NAME := XMLDOM.GETNODEVALUE(XMLDOM.GETFIRSTCHILD(XMLDOM.ITEM(CHILDNODES, 1)));
VALUE := XMLDOM.GETNODEVALUE(XMLDOM.GETFIRSTCHILD(XMLDOM.ITEM(CHILDNODES, 2)));
INSERT INTO XMLTEST VALUES (NEWID, NAME, VALUE);
COMMIT;
END LOOP;
END LOOP;
XMLDOM.FREEDOCUMENT(XDOC);
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END P_PARSE;
XMLDOM:DOM 模型操作(建立元素、屬性、文本節點、序列化)。XMLPARSER:解析 CLOB/VARCHAR2 XML 字串為 DOM 文檔。