PLSQL 解析XML示例1

PLSQL 解析XML示例11：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和tybCREATE 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.pkbCREATE 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,????????????????????????????????? ‘-‘,‘‘)),gc.id,gc1.id,cur.id,?????????????? gac.id,l_request.remittancevalue,l_request.remark,893,?????????????? SYSDATE,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;