oracle xpath 解析xml

oracle xpath 解析xml 样例 ，代码如下

create or replace package body CRM_TEST IS
   PROCEDURE get_PricePlanCode_byid(i_depco_to_localco_id in varchar2,o_PricePlanCode OUT VARCHAR2)
      is
    o_dep_order_type_cd         varchar2(6) := null;
    v_xml                       clob := null;
 
    l_parser xmlparser.parser := null;
    l_doc    xmldom.domdocument := null;
 
    l_nodelist_PricePlanInfo xmldom.domnodelist := null;
    l_node_PricePlanInfo     xmldom.domnode := null;
    prodNbrNode     xmldom.domnode := null;
    l_xpath_PricePlanCode     varchar2(512) := null;
    v_prodNbr VARCHAR2(30);
  begin
    begin
      select  cl.line_xml
        into  v_xml
        from crm.dep_co_2_local_co cl
       where cl.id = i_depco_to_localco_id;
    exception
      when others then
        goto over;
    end;
    begin
      l_parser := xmlparser.newparser;
      xmlparser.parseclob(l_parser, v_xml);
      l_doc := xmlparser.getdocument(l_parser);

   //取单个节点
      prodNbrNode := xslprocessor.selectSingleNode(xmldom.makenode(l_doc),'//CustOrderLine/CustOrderLineInfo/ProductNbr');
      v_prodNbr := xslprocessor .valueof(prodNbrNode,
                                                          './text()');
      dbms_output.put_line('accessNbr:'||v_prodNbr);

//取Nodes
      l_nodelist_PricePlanInfo := xslprocessor.selectnodes(xmldom.makenode(l_doc),
                                                             '//CustOrderLine/PricePlanSET[OrderTypeAction = 10 or OrderTypeAction = 20]/PricePlanInfo');
      l_node_PricePlanInfo     := xmldom.item(l_nodelist_PricePlanInfo,
                                                0);
      l_xpath_PricePlanCode      := './PricePlanCode/text()';
      o_PricePlanCode := xslprocessor.valueof(l_node_PricePlanInfo,
                                                          l_xpath_PricePlanCode);
      xmldom.freedocument(l_doc);
      xmlparser.freeparser(l_parser);
      dbms_output.put_line(o_PricePlanCode);
    exception
      when others then
        goto over;
    end;
    <<over>>
    NULL;
  END ;

end CRM_TEST;

 

xml准备

<CustOrderLine>
 <CustOrderLineInfo>
  <OrderNbr>2010090966363239</OrderNbr>
  <RelaOrderNbr/>
  <ProdCode>CDMA联通133产品</ProdCode>
  <ProductId>2010090966363241</ProductId>
  <ProductNbr>15366113585</ProductNbr>
  <NeedCompleteDt>20100913</NeedCompleteDt>
  <CustOrderType>新装/开户</CustOrderType>
  <CustOrderTypeName/>
  <OrderTypeTime>20100909050928</OrderTypeTime>
  <OrderTypeCd>成员纳入</OrderTypeCd>
  <ForbidUcSp>00</ForbidUcSp>
  <BelongInfo>
   <ProvinceCode>江苏</ProvinceCode>
   <ProvinceName/>
   <CityCode>025</CityCode>
   <CityName/>
  </BelongInfo>
  <OrderNode>
   <OrderNodeCd>改所属(纳入,退出)</OrderNodeCd>
  </OrderNode>
 </CustOrderLineInfo>
 <ManagerSET>
  <ManagerRole>客户经理</ManagerRole>
  <ManagerInfo>
   <ManagerNbr>2010022085619093</ManagerNbr>
   <ManagerName>钱宁珍</ManagerName>
   <ManagerPhoneNbr>02583289626</ManagerPhoneNbr>
   <BelongInfo>
    <ProvinceCode>10</ProvinceCode>
    <ProvinceName/>
    <CityCode>999</CityCode>
    <CityName/>
   </BelongInfo>
  </ManagerInfo>
 </ManagerSET>
 <RelaSET>
  <RelaRoleCode>移动成员. </RelaRoleCode>
  <RelaRoleName>移动成员</RelaRoleName>
  <RelaProductNbr>100011102643</RelaProductNbr>
 </RelaSET>
 <ProdOfferSET>
  <OrderTypeAction>成员号码：退出/资费信息:订购/增值产品:开通/其它：当前</OrderTypeAction>
  <ProdOfferInfo>
   <ProdCode>CDMA联通133产品</ProdCode>
   <ProdType/>
   <ProdName/>
   <ProdOfferCode>CDMA联通133产品</ProdOfferCode>
   <ProdOfferName>CDMA联通133产品</ProdOfferName>
   <BillingMode>后付费</BillingMode>
  </ProdOfferInfo>
 </ProdOfferSET>
 <ProdNbrSET>
  <OrderTypeAction>成员号码：退出/资费信息:订购/增值产品:开通/其它：当前</OrderTypeAction>
  <ProdNbrRole>主接入号</ProdNbrRole>
  <ProdNbrInfo>
   <ProdNbrType>CDMA网联通号码</ProdNbrType>
   <ProdNbr>15366113585</ProdNbr>
   <ProdAreaCode>025</ProdAreaCode>
   <ProdNbrPwd/>
   <ServId>250203813603</ServId>
  </ProdNbrInfo>
 </ProdNbrSET>
 <ProdItemSET>
  <OrderTypeAction>成员号码：退出/资费信息:订购/增值产品:开通/其它：当前</OrderTypeAction>
  <ProdItemInfo>
   <ProdItemCode>VPN成员的短号</ProdItemCode>
   <ProdItemName/>
   <ProdItemDesc/>
   <PropertyInfo>
    <ItemID>0</ItemID>
    <ItemName>短号码</ItemName>
    <ItemValue>729585</ItemValue>
    <ItemValueName/>
   </PropertyInfo>
  </ProdItemInfo>
 </ProdItemSET>
 <PricePlanSET>
  <OrderTypeAction>成员号码：退出/资费信息:订购/增值产品:开通/其它：当前</OrderTypeAction>
  <PricePlanInfo>
   <PricePlanCode>伊利跨省保底100元套餐-CDMA(次月生效)（集团侧：伊利100元套餐）</PricePlanCode>
   <PricePlanName>伊利100元套餐</PricePlanName>
   <PricePlanDesc/>
   <PricePlanType>叠加资费/优惠资费</PricePlanType>
   <PricePlanStartFlag>1</PricePlanStartFlag>
  </PricePlanInfo>
 </PricePlanSET>
 <ServProdSET>
  <OrderTypeAction>成员号码：退出/资费信息:订购/增值产品:开通/其它：当前</OrderTypeAction>
  <ServProdInfo>
   <ServProdCode>呼叫权限(参数:1、无限制,2、号码限制，3、禁止)</ServProdCode>
   <ServProdName>呼叫权限</ServProdName>
   <ServProdType>VPN成员</ServProdType>
   <PropertyInfo>
    <ItemID>呼叫权限值</ItemID>
    <ItemName>呼叫权限</ItemName>
    <ItemValue>1</ItemValue>
    <ItemValueName/>
   </PropertyInfo>
  </ServProdInfo>
 </ServProdSET>
 <TerminalSET>
  <OrderTypeAction>成员号码：退出/资费信息:订购/增值产品:开通/其它：当前</OrderTypeAction>
  <TerminalInfo>
   <TermType>手机</TermType>
   <TermKey>1</TermKey>
   <TermRmID/>
   <TermDesc/>
   <TermModel>30160002</TermModel>
   <TermNum/>
   <TermPrice/>
  </TerminalInfo>
 </TerminalSET>
</CustOrderLine>

测试结果：

accessNbr:15366113585
10022