存储过程中解析xml

在存储过程中需要解析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;