仅解析XML中某个标签中的值
declare
xmlPar xmlparser.Parser := xmlparser.newParser;
xDoc xmldom.DOMDocument;
lenItem INTEGER;
itemNodes xmldom.DOMNodeList;
itemNode xmldom.DOMNode;
v_str varchar(1000);
ValueReturn VARCHAR2 (100);
begin
SELECT MBOUTMSGS_CLOB_MSG into v_str FROM HIS.MBOUTMSGS where ID = 566773730;
xmlparser.parseClob (xmlPar, v_str);
xDoc := xmlparser.getDocument (xmlPar);
xmlparser.freeParser (xmlPar);
itemNodes := xmldom.getElementsByTagName (xDoc, 'META');
itemNode := xmldom.item (itemNodes, 0);
ValueReturn := xmldom.getNodeValue (xmldom.getFirstChild (itemNode));
xmldom.freeDocument(xDoc);
DBMS_OUTPUT.put_line('META' || ':' || ValueReturn);
end;
解析XML并将值插入表中
格式化日期
CREATE OR REPLACE FUNCTION FormatDateValue (key VARCHAR2, value VARCHAR2)
RETURN VARCHAR2
IS
Str VARCHAR2(32);
AA VARCHAR2(32);
DAY VARCHAR2(32);
MOUNTH VARCHAR2(32);
YEAR VARCHAR2(32);
HOUR VARCHAR2(32);
MINUTE VARCHAR2(32);
ValueReturn VARCHAR2 (64);
BEGIN
IF key != ' ' THEN
DAY := SUBSTR(key,0,2);
MOUNTH := SUBSTR(key,3,3);
IF INSTR (MOUNTH,'JAN') > 0 THEN
MOUNTH := 01;
END IF;
IF INSTR (MOUNTH,'FEB') > 0 THEN
MOUNTH := 02;
END IF;
IF INSTR (MOUNTH,'MAR') > 0 THEN
MOUNTH := 03;
END IF;
IF INSTR (MOUNTH,'APR') > 0 THEN
MOUNTH := 04;
END IF;
IF INSTR (MOUNTH,'MAY') > 0 THEN
MOUNTH := 05;
END IF;
IF INSTR (MOUNTH,'JUN') > 0 THEN
MOUNTH := 06;
END IF;
IF INSTR (MOUNTH,'JUL') > 0 THEN
MOUNTH := 07;
END IF;
IF INSTR (MOUNTH,'AUG') > 0 THEN
MOUNTH := 08;
END IF;
IF INSTR (MOUNTH,'SEP') > 0 THEN
MOUNTH := 09;
END IF;
IF INSTR (MOUNTH,'OCT') > 0 THEN
MOUNTH := 10;
END IF;
IF INSTR (MOUNTH,'NOV') > 0 THEN
MOUNTH := 11;
END IF;
IF INSTR (MOUNTH,'DEC') > 0 THEN
MOUNTH := 12;
END IF;
YEAR := SUBSTR(key,6,2);
HOUR := SUBSTR(key,8,2);
MINUTE := SUBSTR(key,-2);
AA := 20;
Str := 0;
IF length(MOUNTH) < 2 THEN
MOUNTH := Str||MOUNTH;
ValueReturn := AA || YEAR || '-' || MOUNTH || '-' || DAY || ' ' || HOUR || ':' || MINUTE;
ELSE
ValueReturn := AA || YEAR || '-' || MOUNTH || '-' || DAY || ' ' || HOUR || ':' || MINUTE;
END IF;
RETURN ValueReturn;
ELSE
ValueReturn := ' ';
RETURN ValueReturn;
END IF;
END FormatDateValue;
解析XML取标签值
CREATE OR REPLACE FUNCTION GetXmlNodeValue_KC (xmlStr CLOB, nodeName VARCHAR2)
RETURN VARCHAR2
IS
xmlPar xmlparser.Parser := xmlparser.newParser;
xDoc xmldom.DOMDocument;
lenItem INTEGER;
itemNodes xmldom.DOMNodeList;
itemNode xmldom.DOMNode;
ValueReturn VARCHAR2 (100);
BEGIN
xmlparser.parseClob (xmlPar, xmlStr);
xDoc := xmlparser.getDocument (xmlPar);
xmlparser.freeParser (xmlPar);
itemNodes := xmldom.getElementsByTagName (xDoc, nodeName);
lenItem := xmldom.getLength (itemNodes);
IF lenItem = 0 THEN
ValueReturn := ' ';
ELSE
itemNode := xmldom.item (itemNodes, 0);
ValueReturn := xmldom.getNodeValue (xmldom.getFirstChild (itemNode));
END IF;
xmldom.freeDocument(xDoc);
RETURN ValueReturn;
END GetXmlNodeValue_KC;
存储过程实现插入
CREATE OR REPLACE PROCEDURE TEST_PARSE_KC(xmlStr IN CLOB) IS
SNDR VARCHAR2(100);
DTTM VARCHAR2(100);
TYPE VARCHAR2(100);
STYP VARCHAR2(100);
FFID VARCHAR2(100);
FBAG VARCHAR2(100);
LBAG VARCHAR2(100);
BEGIN
SNDR := GetXmlNodeValue_KC(xmlStr, 'SNDR');
DTTM := GetXmlNodeValue_KC(xmlStr, 'DTTM');
TYPE := GetXmlNodeValue_KC(xmlStr, 'TYPE');
STYP := GetXmlNodeValue_KC(xmlStr, 'STYP');
FFID := GetXmlNodeValue_KC(xmlStr, 'FFID');
FBAG := GetXmlNodeValue_KC(xmlStr, 'FBAG');
LBAG := GetXmlNodeValue_KC(xmlStr, 'LBAG');
insert into ods.TEST_PARSER(SNDR,DTTM,TYPE,STYP,FFID,FBAG,LBAG)
values(SNDR,DTTM,TYPE,STYP,FFID,FBAG,LBAG)
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END TEST_PARSE_KC;
执行
DECLARE
CURSOR c_cursor
IS
SELECT MBOUTMSGS_CLOB_MSG FROM HIS.MBOUTMSGS;
v_MBOUTMSGS_CLOB_MSG HIS.MBOUTMSGS.MBOUTMSGS_CLOB_MSG%TYPE;
BEGIN
OPEN c_cursor;
FETCH c_cursor
INTO v_MBOUTMSGS_CLOB_MSG;
WHILE c_cursor%FOUND LOOP
DBMS_OUTPUT.put_line(v_MBOUTMSGS_CLOB_MSG);
FETCH c_cursor
INTO v_MBOUTMSGS_CLOB_MSG;
TEST_PARSE(v_MBOUTMSGS_CLOB_MSG);
END LOOP;
END;