Extract xml value from Oracle SQL field which is xml with namespace

Question

one my fields in a oracle table looks like the snippet. i want to extract oldMSISDN,newMSISDN and BAN from this xml using oracle query. ( Not unix./perl/etc )

<?xml version="1.0" encoding="UTF-8"?>
<body>
    <ns0:requestHeader xmlns:ns0="http://www.abcdegh.com/csi/base">
        <ns0:applicationId>CSP</ns0:applicationId>
        <ns0:channelId>31</ns0:channelId>
        <ns0:operatorId>1991</ns0:operatorId>
        <ns0:clientId>COLLECT</ns0:clientId>
    </ns0:requestHeader>
    <ns0:BAN xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">2346765421</ns0:BAN>
    <ns0:oldMSISDN xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">9871132132</ns0:oldMSISDN>
    <ns0:newMSISDN xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">3146745341</ns0:newMSISDN>
    <ns0:dealerCode xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">5196637</ns0:dealerCode>
    <ns0:reasonCode xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">PR</ns0:reasonCode>
    <ns0:memoText xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn"/>
    <ns0:portInFlag xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">Y</ns0:portInFlag>
    <ns0:portData xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">
        <ns1:dealerCode xmlns:ns1="http://www.abcdegh.com/co/wlnp/portin">5196637</ns1:dealerCode>
        <ns1:dueDateTime xmlns:ns1="http://www.abcdegh.com/co/wlnp/portin">20170216000000</ns1:dueDateTime>
        <ns1:eligCheckCompleted xmlns:ns1="http://www.abcdegh.com/co/wlnp/portin">true</ns1:eligCheckCompleted>
        <ns1:portCompleted xmlns:ns1="http://www.abcdegh.com/co/wlnp/portin">true</ns1:portCompleted>
    </ns0:portData>
</body>

i want to extract oldMSISDN,newMSISDN and BAN from this xml. can someone help

docs.oracle.com/cd/E18930_01/html/821-2440/aeqey.html

Martin Zeitler
– Martin Zeitler

2017-02-18 04:14:22 +00:00
Commented Feb 18, 2017 at 4:14 — Martin Zeitler
– Martin Zeitler, Commented Feb 18, 2017 at 4:14

mark d drake · Accepted Answer · 2017-04-10 16:09:00Z

Here you go

SQL> with MYTABLE as
  2  (
  3  select XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
  4  <body>
  5      <ns0:requestHeader xmlns:ns0="http://www.abcdegh.com/csi/base">
  6          <ns0:applicationId>CSP</ns0:applicationId>
  7          <ns0:channelId>31</ns0:channelId>
  8          <ns0:operatorId>1991</ns0:operatorId>
  9          <ns0:clientId>COLLECT</ns0:clientId>
 10      </ns0:requestHeader>
 11      <ns0:BAN xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">2346765421</ns0:BAN>
 12      <ns0:oldMSISDN xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">9871132132</ns0:oldMSISDN>
 13      <ns0:newMSISDN xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">3146745341</ns0:newMSISDN>
 14      <ns0:dealerCode xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">5196637</ns0:dealerCode>
 15      <ns0:reasonCode xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">PR</ns0:reasonCode>
 16      <ns0:memoText xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn"/>
 17      <ns0:portInFlag xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">Y</ns0:portInFlag>
 18      <ns0:portData xmlns:ns0="http://www.abcdegh.com/csi/subscriber/changemsisdn">
 19          <ns1:dealerCode xmlns:ns1="http://www.abcdegh.com/co/wlnp/portin">5196637</ns1:dealerCode>
 20          <ns1:dueDateTime xmlns:ns1="http://www.abcdegh.com/co/wlnp/portin">20170216000000</ns1:dueDateTime>
 21          <ns1:eligCheckCompleted xmlns:ns1="http://www.abcdegh.com/co/wlnp/portin">true</ns1:eligCheckCompleted>
 22          <ns1:portCompleted xmlns:ns1="http://www.abcdegh.com/co/wlnp/portin">true</ns1:portCompleted>
 23      </ns0:portData>
 24  </body>') as XMLDOC from dual
 25  )
 26  select oldMSISDN, newMSISDN, BAN
 27    from MYTABLE,
 28         XMLTABLE(
 29           xmlnamespaces('http://www.abcdegh.com/csi/subscriber/changemsisdn' as "ns0"),
 30           '/body'
 31           passing XMLDOC
 32           columns
 33              BAN number(10) path 'ns0:BAN',
 34              oldMSISDN number(10) path 'ns0:oldMSISDN',
 35              newMSISDN number(10) path 'ns0:newMSISDN'
 36         )
 37  /
9871132132 3146745341 2346765421

SQL>

Collectives™ on Stack Overflow

Extract xml value from Oracle SQL field which is xml with namespace

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related