PLSQL 解析XML示例2
1:PLSQL解析有嵌套的复杂XML数据，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:test>
<ns1:Remark1>123</ns1:Remark1>
<ns1:Remark1>456</ns1:Remark1>
</ns1:test>
<orderLines>
<orderLine>
<orderLineNo></orderLineNo>
<ownerCode>QBXA019</ownerCode>
<itemCode>27313011617033329</itemCode>
<planQty>0</planQty>
<actualQty>1</actualQty>
</orderLine>
<orderLine>
<orderLineNo></orderLineNo>
<ownerCode>QBXA019</ownerCode>
<itemCode>27313011617033339</itemCode>
<planQty>0</planQty>
<actualQty>1</actualQty>
</orderLine>
</orderLines>
</ns1:RemittanceParameterItem>
</ns1:parameters>
</ns1:IncreaseCreditAfterRemittance>
2：编写解析程序，increasecreditafterremittance为自定义type:
CREATE OR REPLACE FUNCTION decd_incr_creditafremittance1(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);
l_children1 dbms_xmldom.domnodelist;
l_length1 INTEGER;
l_child1 dbms_xmldom.domnode;
idx INTEGER;
l_children2 dbms_xmldom.domnodelist;
l_length2 INTEGER;
l_child2 dbms_xmldom.domnode;
/* l_children3 dbms_xmldom.domnodelist;
l_length3 INTEGER;
l_child3 dbms_xmldom.domnode;*/
v_test VARCHAR2(255);
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 := soapdeccommon.decode_string(l_child);
l_obj.leaguecompanyid := l_decode_result;
END IF;
IF l_name = 'CompanyId' THEN
l_decode_result := soapdeccommon.decode_string(l_child);
l_obj.companyid := l_decode_result;
END IF;
IF l_name = 'RemittanceValue' THEN
l_decode_result := soapdeccommon.decode_int(l_child);
l_obj.remittancevalue := l_decode_result;
END IF;
IF l_name = 'RemittanceToken' THEN
l_decode_result := soapdeccommon.decode_string(l_child);
l_obj.remittancetoken := l_decode_result;
END IF;
IF l_name = 'RemittanceDateTime' THEN
l_decode_result := soapdeccommon.decode_string(l_child);
l_obj.remittancedatetime := l_decode_result;
END IF;
IF l_name = 'Remark' THEN
l_decode_result := soapdeccommon.decode_string(l_child);
l_obj.remark := l_decode_result;
END IF;
IF l_name = 'test1' THEN
/*raise_application_error(-20201,
'soapdeccommon.decode_string(l_child1)');*/
l_children1 := dbms_xmldom.getchildnodes(l_child);
l_length1 := dbms_xmldom.getlength(l_children1);
FOR idx IN 0 .. (l_length1 - 1) LOOP
l_child1 := dbms_xmldom.item(l_children1, idx);
IF dbms_xmldom.getnodetype(l_child1) !=
dbms_xmldom.element_node THEN
CONTINUE;
END IF;
l_name := dbms_xmldom.getlocalname(dbms_xmldom.makeelement(l_child1));
IF l_name = 'Remark1' THEN
v_test := v_test || '--' ||
soapdeccommon.decode_string(l_child1);
END IF;
END LOOP;
raise_application_error(-20201, 'v_test:' || v_test);
END IF;
IF l_name = 'orderLines' THEN
l_children1 := dbms_xmldom.getchildnodes(l_child);
l_length1 := dbms_xmldom.getlength(l_children1);
FOR idx IN 0 .. (l_length1 - 1) LOOP
l_child1 := dbms_xmldom.item(l_children1, idx);
IF dbms_xmldom.getnodetype(l_child1) !=
dbms_xmldom.element_node THEN
CONTINUE;
END IF;
l_name := dbms_xmldom.getlocalname(dbms_xmldom.makeelement(l_child1));
IF l_name = 'orderLine' THEN
l_children2 := dbms_xmldom.getchildnodes(l_child1);
l_length2 := dbms_xmldom.getlength(l_children2);
FOR idx1 IN 0 .. (l_length2 - 1) LOOP
l_child2 := dbms_xmldom.item(l_children2, idx1);
IF dbms_xmldom.getnodetype(l_child2) !=
dbms_xmldom.element_node THEN
CONTINUE;
END IF;
l_name := dbms_xmldom.getlocalname(dbms_xmldom.makeelement(l_child2));
IF l_name = 'itemCode' THEN
v_test := v_test || '--' ||
soapdeccommon.decode_string(l_child2);
END IF;
IF l_name = 'actualQty' THEN
v_test := v_test || '--' ||
soapdeccommon.decode_string(l_child2);
END IF;
END LOOP;
END IF;
END LOOP;
raise_application_error(-20201, 'v_test:' || v_test);
END IF;
END LOOP;
RETURN l_obj;
END;
3：测试用例
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:test>
<ns1:Remark1>123</ns1:Remark1>
<ns1:Remark1>456</ns1:Remark1>
</ns1:test>
<orderLines>
<orderLine>
<orderLineNo></orderLineNo>
<ownerCode>QBXA019</ownerCode>
<itemCode>27313011617033329</itemCode>
<planQty>0</planQty>
<actualQty>1</actualQty>
</orderLine>
<orderLine>
<orderLineNo></orderLineNo>
<ownerCode>QBXA019</ownerCode>
<itemCode>27313011617033339</itemCode>
<planQty>0</planQty>
<actualQty>1</actualQty>
</orderLine>
</orderLines>
</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 := decd_incr_creditafremittance1(requestNode);
--raise_application_error(-20201,'l_request.LeagueCompanyId:'||l_request.leaguecompanyid||'--'||'l_request.CompanyId:'||l_request.CompanyId||'--'||'l_request.RemittanceValue:'||l_request.RemittanceValue);
end;
浙公网安备 33010602011771号