存储过程中解析xml
来源:互联网 发布:python双引号转义字符 编辑:程序博客网 时间:2024/05/18 03:48
在存储过程中需要解析xml入参,找了很多资料,终于搞定,第一次吗,记下以备后用。
CREATE OR REPLACE PROCEDURE P_C_BSF_GETECUSTOMINFOLIST/* Description :?????? Author : zuowenwu Date : 2012-06-13 Version : v1.00 Caller : Callee : Comments : */(serviceId in varchar2, o_ret OUT NUMBER, rCursor OUT sys_refcursor) IS v_next_val varchar2(30); isSuccess varchar2(10); CALLERNO VARCHAR2(50); CUSTOMERNAME VARCHAR2(50); ADDRESS varchar2(200); CONTRACT_TYPE varchar2(10); TP varchar2(10); BILL varchar2(50); OUTSTANDING varchar2(50); SIM varchar2(100); IMSI varchar2(100); PUK1 varchar2(100); PUK2 varchar2(100); CALL_DETAILS_0 varchar2(100); CALL_DETAILS_1 varchar2(100); CALL_DETAILS_2 varchar2(100); CALL_DETAILS_3 varchar2(100); CALL_DETAILS_4 varchar2(100); CALL_DETAILS_5 varchar2(100); CALL_DETAILS_6 varchar2(100); CALL_DETAILS_7 varchar2(100); CALL_DETAILS_8 varchar2(100); CALL_DETAILS_9 varchar2(100); op_response_object_x XMLTYPE;BEGIN --get xmltype of response from procedure GET_CUSTOMER_DETAILS insert into CSPlOG values('Calling the GET_CUSTOMER_DETAILS'); commit; GET_CUSTOMER_DETAILS('',serviceId,3,op_response_object_x); -- if REQUEST_STATUS = 0:success 102: fail select i.xmldoc.extract('/EVENT/API_OUTPUT/REQUEST_STATUS/text()') .getStringVal() as ennames INTO isSuccess --from (SELECT T.XMLDOC AS XMLDOC FROM ABC T WHERE T.ID = 2) i; from (SELECT op_response_object_x AS XMLDOC FROM DUAL T) i; insert into CSPlOG values('GOT THE XML OBJ'); commit; IF isSuccess = '0' then select i.xmldoc.extract('/EVENT/ROWSET/ROW/ICALLERNO/text()') .getStringVal() as ICALLERNO, i.xmldoc.extract('/EVENT/ROWSET/ROW/NAME/text()') .getStringVal() as CUSTOMERNAME, i.xmldoc.extract('/EVENT/ROWSET/ROW/ADDRESS/ADDRESS_INFO/ADDRESS_DTLS/@ADDRESS') .getStringVal() as ADDRESS, i.xmldoc.extract('/EVENT/ROWSET/ROW/CONTRACT_TYPE/text()') .getStringVal() as CONTRACT_TYPE, i.xmldoc.extract('/EVENT/ROWSET/ROW/TP/text()') .getStringVal() as TP, i.xmldoc.extract('/EVENT/ROWSET/ROW/BILL/text()') .getStringVal() as BILL, i.xmldoc.extract('/EVENT/ROWSET/ROW/OUTSTANDING/text()') .getStringVal() as OUTSTANDING, i.xmldoc.extract('/EVENT/ROWSET/ROW/SIM/text()') .getStringVal() as SIM, i.xmldoc.extract('/EVENT/ROWSET/ROW/IMSI/text()') .getStringVal() as IMSI, i.xmldoc.extract('/EVENT/ROWSET/ROW/PUK1/text()') .getStringVal() as PUK1, i.xmldoc.extract('/EVENT/ROWSET/ROW/PUK2/text()') .getStringVal() as PUK2, i.xmldoc.extract('/EVENT/ROWSET/ROW/CALL_DETAILS/CALL_DETAILS_0/text()') .getStringVal() as CALL_DETAILS_0, i.xmldoc.extract('/EVENT/ROWSET/ROW/CALL_DETAILS/CALL_DETAILS_1/text()') .getStringVal() as CALL_DETAILS_1, i.xmldoc.extract('/EVENT/ROWSET/ROW/CALL_DETAILS/CALL_DETAILS_2/text()') .getStringVal() as CALL_DETAILS_2, i.xmldoc.extract('/EVENT/ROWSET/ROW/CALL_DETAILS/CALL_DETAILS_3/text()') .getStringVal() as CALL_DETAILS_3, i.xmldoc.extract('/EVENT/ROWSET/ROW/CALL_DETAILS/CALL_DETAILS_4/text()') .getStringVal() as CALL_DETAILS_4, i.xmldoc.extract('/EVENT/ROWSET/ROW/CALL_DETAILS/CALL_DETAILS_5/text()') .getStringVal() as CALL_DETAILS_5, i.xmldoc.extract('/EVENT/ROWSET/ROW/CALL_DETAILS/CALL_DETAILS_6/text()') .getStringVal() as CALL_DETAILS_6, i.xmldoc.extract('/EVENT/ROWSET/ROW/CALL_DETAILS/CALL_DETAILS_7/text()') .getStringVal() as CALL_DETAILS_7, i.xmldoc.extract('/EVENT/ROWSET/ROW/CALL_DETAILS/CALL_DETAILS_8/text()') .getStringVal() as CALL_DETAILS_8, i.xmldoc.extract('/EVENT/ROWSET/ROW/CALL_DETAILS/CALL_DETAILS_9/text()') .getStringVal() as CALL_DETAILS_9 INTO CALLERNO, CUSTOMERNAME, ADDRESS, CONTRACT_TYPE, TP, BILL, OUTSTANDING, SIM, IMSI, PUK1, PUK2, CALL_DETAILS_0, CALL_DETAILS_1, CALL_DETAILS_2, CALL_DETAILS_3, CALL_DETAILS_4, CALL_DETAILS_5, CALL_DETAILS_6, CALL_DETAILS_7, CALL_DETAILS_8, CALL_DETAILS_9 --from (SELECT T.XMLDOC AS XMLDOC FROM ABC T WHERE T.ID = 2) i; from (SELECT op_response_object_x AS XMLDOC FROM DUAL T) i; insert into CSPlOG values('SETTING CUSTOMER DETAILS'); commit; OPEN rCursor FOR SELECT CUSTOMERNAME as CUSTOMERNAME, ADDRESS as CUSTOMERADDRESS, TP as Topup, BILL as BILL, OUTSTANDING as OUTSTANDING, IMSI as IMSI, PUK1 as PUK1, PUK2 as PUK2, SIM as SIM, CALL_DETAILS_0 || '|' || CALL_DETAILS_2 || '|' || CALL_DETAILS_2 || '|' || CALL_DETAILS_3 || '|' || CALL_DETAILS_4 || '|' || CALL_DETAILS_5 || '|' || CALL_DETAILS_6 || '|' || CALL_DETAILS_7 || '|' || CALL_DETAILS_8 || '|' || CALL_DETAILS_9 as LastCallMode, CONTRACT_TYPE as CONTRACTTYPE, '' as DuePayment FROM DUAL; o_ret := 0; else insert into CSPlOG values('DISPLAYIING CUSTOMER_DETAILS'); commit; -- response is fail OPEN rCursor FOR SELECT '' as CUSTOMERNAME, '' as CUSTOMERADDRESS, '' as Topup, '' as BILL, '' as OUTSTANDING, '' as IMSI, '' as PUK1, '' as PUK2, '' as SIM, '' as LastCallMode, '' as CONTRACTTYPE, '' as DuePayment FROM DUAL; o_ret := 102; end if;EXCEPTION WHEN OTHERS THEN o_ret := 111; insert into CSPlOG values('IN EXCECPTION'); commit; ROLLBACK;END P_C_BSF_GETECUSTOMINFOLIST;
还有一个一起附上:
CREATE OR REPLACE PROCEDURE PARSER_XML_TEST(file_path VARCHAR2, log_path VARCHAR2) AS --//XML解析器 xmlPar XMLPARSER.parser := XMLPARSER.NEWPARSER; --//DOM文档对象 doc xmldom.DOMDocument; len integer; personNodes xmldom.DOMNodeList; chilNodes xmldom.DOMNodeList; tempNode xmldom.DOMNode; tempArrMap xmldom.DOMNamedNodeMap; --================================ --以下变量用于获取XML节点的值 pid varchar2(4); name varchar2(50); address varchar2(200); tel varchar2(20); fax varchar2(20); email varchar(100); tmp integer; --================================BEGIN xmlPar := xmlparser.newParser; xmlparser.setErrorLog(xmlPar, log_path); xmlparser.parse(xmlPar, file_path); doc := xmlparser.getDocument(xmlPar); -- 释放解析器实例 xmlparser.freeParser(xmlPar); -- 获取所有PERSON元素 personNodes := xmldom.getElementsByTagName(doc, 'PERSON'); len := xmldom.getLength(personNodes); --遍历所有PERSON元素 FOR i in 0 .. len - 1 LOOP --获取第i个PERSON tempNode := xmldom.item(personNodes, i); --所有属性 tempArrMap := xmldom.getAttributes(tempNode); --获取PERSONID的值 pid := xmldom.getNodeValue(xmldom.getNamedItem(tempArrMap, 'PERSONID')); --获取子元素的值 chilNodes := xmldom.getChildNodes(tempNode); tmp := xmldom.GETLENGTH(chilNodes); name := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 0))); address := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 1))); tel := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 2))); fax := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 3))); email := xmldom.getNodeValue(xmldom.getFirstChild(xmldom.item(chilNodes, 4))); --插入数据 INSERT INTO PEOPLE VALUES (pid, name, address, tel, fax, email); COMMIT; END LOOP; -- 释放文档对象 xmldom.freeDocument(doc);EXCEPTION WHEN OTHERS THEN DBMS_output.PUT_LINE(SQLERRM);END PARSER_XML_TEST;
- 存储过程中解析xml
- Oracle 存储过程 解析xml
- oracle中使用存储过程解析xml字符串
- SQL Server 存储过程解析XML传参
- Oracle存储过程中使用游标来批量解析CLOB字段里面的xml字符串:
- Oracle中通过:触发器,存储过程,Function调用实现解析Clob字段类型中存储的xml字符串
- SQLServer-存储过程中xml的处理
- Android中Xml数据存储与解析
- 解析存储过程中常见变量
- 解析存储过程中常见变量
- Oracle中通过Function,存储过程,触发器,调用实现解析Clob字段中存在的xml字符串
- Oracle中通过游标执行带参数的存储过程实现解析CLOB字段内的xml字符串:
- [XML]sax中DefaultHander解析xml过程和先后顺序
- xml文档存储过程
- android--存储XML解析
- 存储过程大解析
- MySQL存储过程解析
- sql解析存储过程
- 超越想象—windows 8应用设计与开发
- List of Eclipse-based software
- 编译移植Mplayer到mini2440开发板的过程及问题解决办法
- 你的网站赚钱吗——写给4000万中小企业的网络营销工具书
- C# datagridView 鼠标不离开无法更新的问题解决
- 存储过程中解析xml
- 《权力》读书笔记,第4章 成为最引人注目的那一个
- c语言基本数据类型short、int、long、char、float、double
- Windows8 String 和 Buffer之间的转化
- 详解 QT 皮肤 QSS编程(2)
- ASP.NET MVC 4 框架揭秘
- 代码整洁之道------它山之玉可以重构:身份证号码解析、验证工具(第一天)
- HMaster自动退出源于regionServer宕机
- 透明窗体 使用DWM实现Aero Glass效果