PLSQL 解析XML示例1
1：xml数据格式如下：
<ns1:IncreaseCreditAfterRemittance xmlns:ns1="http://tempuri.org/"> <ns1:parameters> <ns1:RemittanceParameterItem> <ns1:LeagueCompanyId>2001116</ns1:LeagueCompanyId> <ns1:CompanyId>1000</ns1:CompanyId> <ns1:RemittanceValue>65000.0</ns1:RemittanceValue> <ns1:RemittanceToken>9591503</ns1:RemittanceToken> <ns1:RemittanceDateTime>2018-09-26T14:51:22.0Z</ns1:RemittanceDateTime> <ns1:Remark>640001391210002018</ns1:Remark> </ns1:RemittanceParameterItem> </ns1:parameters> </ns1:IncreaseCreditAfterRemittance>
2：定义type increasecreditafterremittance来暂存解析出来的各字段，这里以xml根节点名称来命名类型，如下：typ和tyb
CREATE OR REPLACE TYPE increasecreditafterremittance AS OBJECT
(
leaguecompanyid VARCHAR2(4000),
companyid VARCHAR2(4000),
remittancevalue INTEGER,
remittancetoken VARCHAR2(32767),
remittancedatetime VARCHAR2(32767),
remark VARCHAR2(32767),
CONSTRUCTOR FUNCTION increasecreditafterremittance RETURN SELF AS RESULT
);
CREATE OR REPLACE TYPE BODY increasecreditafterremittance AS
CONSTRUCTOR FUNCTION increasecreditafterremittance RETURN SELF AS RESULT IS
BEGIN
RETURN;
END;
END;
3:定义对应的解析包程序，把一些公共的解析程序放入公用包中，提高代码复用，如下：分别定义soapdeccommon.pkg和soapdeccommon.pkb
CREATE OR REPLACE PACKAGE soapdeccommon IS
------------------------------------
--author:xy
--date:20181009
--description:用于解析XML中具体类型字段
------------------------------------
FUNCTION decode_string(p_node IN dbms_xmldom.domnode) RETURN VARCHAR2;
FUNCTION decode_int(p_node IN dbms_xmldom.domnode) RETURN INTEGER;
FUNCTION decode_integer(p_node IN dbms_xmldom.domnode) RETURN INTEGER;
FUNCTION decode_calendar(p_node IN dbms_xmldom.domnode) RETURN TIMESTAMP
WITH TIME ZONE;
FUNCTION decd_incr_creditafremittance(p_node IN dbms_xmldom.domnode)
RETURN increasecreditafterremittance;
END soapdeccommon;
CREATE OR REPLACE PACKAGE BODY soapdeccommon IS
------------------------------------
--author:xy
--date:20181009
--description:用于解析XML中具体类型字段
------------------------------------
FUNCTION decode_string(p_node IN dbms_xmldom.domnode) RETURN VARCHAR2 IS
l_children dbms_xmldom.domnodelist;
l_length INTEGER;
l_i INTEGER;
l_child dbms_xmldom.domnode;
l_char_data dbms_xmldom.domcharacterdata;
BEGIN
l_children := dbms_xmldom.getchildnodes(p_node);
l_length := dbms_xmldom.getlength(l_children);
l_i := 0;
WHILE l_i < l_length LOOP
l_child := dbms_xmldom.item(l_children, l_i);
IF dbms_xmldom.getnodetype(l_child) = dbms_xmldom.text_node THEN
l_char_data := dbms_xmldom.makecharacterdata(l_child);
RETURN dbms_xmldom.getdata(l_char_data);
END IF;
l_i := l_i + 1;
END LOOP;
RETURN '';
END;
FUNCTION decode_int(p_node IN dbms_xmldom.domnode) RETURN INTEGER IS
BEGIN
RETURN to_number(decode_string(p_node));
END;
FUNCTION decode_integer(p_node IN dbms_xmldom.domnode) RETURN INTEGER IS
BEGIN
RETURN to_number(decode_string(p_node));
END;
FUNCTION decode_calendar(p_node IN dbms_xmldom.domnode) RETURN TIMESTAMP
WITH TIME ZONE IS
l_decode_result VARCHAR2(32767);
l_calendar TIMESTAMP WITH TIME ZONE;
BEGIN
l_decode_result := decode_string(p_node);
l_calendar := NULL;
BEGIN
l_calendar := to_timestamp_tz(l_decode_result,
'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM');
EXCEPTION
WHEN OTHERS THEN
BEGIN
BEGIN
l_calendar := to_timestamp_tz(l_decode_result,
'YYYY-MM-DD"T"HH24:MI:SS');
EXCEPTION
WHEN OTHERS THEN
BEGIN
l_calendar := to_timestamp_tz(l_decode_result,
'YYYY-MM-DD"T"HH24:MI:SS"Z"');
END;
END;
END;
END;
RETURN l_calendar;
END;
FUNCTION decd_incr_creditafremittance(p_node IN dbms_xmldom.domnode)
RETURN increasecreditafterremittance IS
l_obj increasecreditafterremittance;
l_children dbms_xmldom.domnodelist;
l_length INTEGER;
l_i INTEGER;
l_child dbms_xmldom.domnode;
l_name VARCHAR2(32767);
l_decode_result VARCHAR2(32767);
BEGIN
l_obj := increasecreditafterremittance();
l_children := dbms_xmldom.getchildnodes(p_node);
l_length := dbms_xmldom.getlength(l_children);
FOR l_i IN 0 .. (l_length - 1) LOOP
l_child := dbms_xmldom.item(l_children, l_i);
IF dbms_xmldom.getnodetype(l_child) != dbms_xmldom.element_node THEN
CONTINUE;
END IF;
l_name := dbms_xmldom.getlocalname(dbms_xmldom.makeelement(l_child));
IF l_name = 'LeagueCompanyId' THEN
l_decode_result := decode_string(l_child);
l_obj.leaguecompanyid := l_decode_result;
END IF;
IF l_name = 'CompanyId' THEN
l_decode_result := decode_string(l_child);
l_obj.companyid := l_decode_result;
END IF;
IF l_name = 'RemittanceValue' THEN
l_decode_result := decode_int(l_child);
l_obj.remittancevalue := l_decode_result;
END IF;
IF l_name = 'RemittanceToken' THEN
l_decode_result := decode_string(l_child);
l_obj.remittancetoken := l_decode_result;
END IF;
IF l_name = 'RemittanceDateTime' THEN
l_decode_result := decode_string(l_child);
l_obj.remittancedatetime := l_decode_result;
END IF;
IF l_name = 'Remark' THEN
l_decode_result := decode_string(l_child);
l_obj.remark := l_decode_result;
END IF;
END LOOP;
RETURN l_obj;
END;
END soapdeccommon;
4:编写测试用例进行测试，如下测试用例：
declare request_clob clob :='<ns1:IncreaseCreditAfterRemittance xmlns:ns1="http://tempuri.org/"> <ns1:parameters> <ns1:RemittanceParameterItem> <ns1:LeagueCompanyId>2001116</ns1:LeagueCompanyId> <ns1:CompanyId>1000</ns1:CompanyId> <ns1:RemittanceValue>65000.0</ns1:RemittanceValue> <ns1:RemittanceToken>9591503</ns1:RemittanceToken> <ns1:RemittanceDateTime>2018-09-26T14:51:22.0Z</ns1:RemittanceDateTime> <ns1:Remark>640001391210002018</ns1:Remark> </ns1:RemittanceParameterItem> </ns1:parameters> </ns1:IncreaseCreditAfterRemittance>'; req_doc DBMS_XMLDOM.domdocument; requestNode XMLDOM.domnode; l_request increasecreditafterremittance; begin l_request := increasecreditafterremittance(); req_doc := dbms_xmldom.newDOMDocument (request_clob); requestNode := XMLDOM.item (xmldom.getelementsbytagname (req_doc,'RemittanceParameterItem'), 0); l_request := soapdeccommon.decd_incr_creditafremittance(requestNode); raise_application_error(-20201,'l_request.LeagueCompanyId:'||l_request.leaguecompanyid||'--'||'l_request.CompanyId:'||l_request.CompanyId||'--'||'l_request.RemittanceValue:'||l_request.RemittanceValue); end;
PLSQL DEVELOPER运行测试用例，得出如下图所示结果：
5：经过上述测试，则对于接收来自调用者的xml信息，解析并进行数据库相关操作，如下存储过程功能即：解析接收的xml，对其进行分析，然后插入相关数据库表中
CREATE OR REPLACE PROCEDURE incrcreditafremittance_read(p_request_clob IN CLOB,
p_code IN OUT NUMBER,
p_err_mesg IN OUT VARCHAR2) AS
------------------------------------------------
--author:xy
--date:20181009
--description:获取RFC接口下发的电子付款单，向公司收款单中写入
------------------------------------------------
req_doc dbms_xmldom.domdocument;
requestnode xmldom.domnode;
l_request increasecreditafterremittance;
v_table_id ad_table.id%TYPE; --公司收款单g_receive表的id
v_docno g_receive.docno%TYPE; ----公司收款单g_receive单据编号
v_g_receive_id g_receive.id%TYPE;
BEGIN
p_code := 1;
p_err_mesg := 'FAILED:INSERTION FAILED';
l_request := increasecreditafterremittance();
req_doc := dbms_xmldom.newdomdocument(p_request_clob);
requestnode := xmldom.item(xmldom.getelementsbytagname(req_doc,
'RemittanceParameterItem'),
0);
l_request := soapdeccommon.decd_incr_creditafremittance(requestnode);
--获取表g_receive的id
SELECT id
INTO v_table_id
FROM ad_table
WHERE NAME = upper('g_receive');
--自动生成单据编号
SELECT t.sequencename
INTO v_docno
FROM ad_column t
WHERE t.ad_table_id = v_table_id
AND t.dbname = 'DOCNO';
v_docno := get_sequenceno(v_docno, 37);
v_g_receive_id := get_sequences('G_RECEIVE');
/*raise_application_error(-20201,
to_number(REPLACE(substr(l_request.remittancedatetime,
1, 10), '-', '')));*/
INSERT INTO g_receive
(id, ad_client_id, ad_org_id, docno, billdate, g_company_id,
g_acc_company_id, c_currency_id, c_tranrency_id, g_acctype_id,
feereceive, description, status, ownerid, modifierid, creationdate,
statuserid, statustime, modifieddate, isactive)
SELECT v_g_receive_id, 37, 27, v_docno,
to_number(REPLACE(substr(l_request.remittancedatetime, 1, 10),
'-', '')), gc.id, gc1.id, cur.id, cur.id,
gac.id, l_request.remittancevalue, l_request.remark, 1, 893, 893,
SYSDATE, NULL, NULL, SYSDATE, 'Y'
FROM dual
JOIN g_company gc
ON (gc.sap_code = l_request.leaguecompanyid)
JOIN g_company gc1
ON (gc1.sap_code = l_request.companyid)
JOIN c_currency cur
ON (cur.iso_code = 'CNY')
JOIN g_acctype gac
ON (gac.code = '001' AND gac.name = '默认账户');
IF SQL%ROWCOUNT > 0 THEN
g_receive_submit(v_g_receive_id, p_code, p_err_mesg);
p_code := 0;
p_err_mesg := 'SUCCESS';
END IF;
EXCEPTION
WHEN OTHERS THEN
p_code := 1;
p_err_mesg := 'FAILED:' || SQLERRM;
END;
