How can I extract the first node value from a XMLTYPE?

Question

I have a xml which is in a clob field of my table

This is the code I am currently using:

create table
   xml_key
(  
   mycol number,
   xml   clob
);


insert into xml_key
values(1653,
'<AppHdr>
    <Fr>
      <FIId>
        <FinInstnId>
          <BICFI>CRESUS33</BICFI>
        </FinInstnId>
      </FIId>
    </Fr>
    <To>
      <FIId>
        <FinInstnId>
          <BICFI>CAYTTRISXXX</BICFI>
        </FinInstnId>
      </FIId>
    </To>
    <BizMsgIdr>e9aab4a5-bc32-4548-93f6-aa47dd97d77</BizMsgIdr>
    <MsgDefIdr>pac.004.001.09</MsgDefIdr>
    <BizSvc>swift.cbprplus.02</BizSvc>
    <CreDt>2019-08-12T13:59:26+01:00</CreDt>
    <Rltd>
      <Fr>
        <FIId>
          <FinInstnId>
            <BICFI>LOYDGB21002</BICFI>
          </FinInstnId>
        </FIId>
      </Fr>
      <To>
        <FIId>
          <FinInstnId>
            <BICFI>CRESUS33</BICFI>
          </FinInstnId>
        </FIId>
      </To>
      <BizMsgIdr>1234567890</BizMsgIdr>
        <MsgDefIdr>pacs.008.001.08</MsgDefIdr>
            <CreDt>2019-08-12T12:59:26+01:00</CreDt>
        </Rltd>
    </AppHdr>');

when i extract BIC code from xml i have a problem. I don't want double nodes like CRESUS33LOYDGB21002 How can I extract only CRESUS33 as FromBIC from xml?

 select XMLTYPE(t.xml).EXTRACT('//Fr/FIId/FinInstnId/BICFI/text()').getStringVal()  fromBIC,
     XMLTYPE(t.xml).EXTRACT('//Rltd/Fr/FIId/FinInstnId/BICFI/text()').getStringVal()  RltdFromBIC
          from xml_key t
         where mycol=1653 ;
    
    FROMBIC                           RLTDFROMBIC
    --------------------------------- --------------------------------------
    CRESUS33LOYDGB21002               LOYDGB21002

Thank you

Answer 1

The problem is you are using //Fr as a shortcut in both extract calls, and as Fr is a node both directly under AppHdr and under AppHdr/Rltd the first XPath matches both of those, and you end up with the values for both combined. The second Xpath is more selective so that only sees the second one.

If you give the full path for both then you won't have that issue:

 select XMLTYPE(t.xml).EXTRACT('/AppHdr/Fr/FIId/FinInstnId/BICFI/text()').getStringVal()  fromBIC,
     XMLTYPE(t.xml).EXTRACT('/AppHdr/Rltd/Fr/FIId/FinInstnId/BICFI/text()').getStringVal()  RltdFromBIC
          from xml_key t
         where mycol=1653 ;

You can also use XMLQuery:

select
  XMLQuery('/AppHdr/Fr/FIId/FinInstnId/BICFI/text()'
    passing XMLTYPE(t.xml)
    returning content).getStringVal() as fromBIC,
  XMLQuery('/AppHdr/Rltd/Fr/FIId/FinInstnId/BICFI/text()'
    passing XMLTYPE(t.xml)
    returning content).getStringVal() as RltdFromBIC
from xml_key t
where mycol=1653;

or XMLTable:

select x.fromBIC, x.RltdFromBIC
from xml_key t
cross apply XMLTable(
  '/AppHdr'
  passing XMLTYPE(t.xml)
  columns
    fromBIC path 'Fr/FIId/FinInstnId/BICFI',
    RltdFromBIC path 'Rltd/Fr/FIId/FinInstnId/BICFI'
) x
where mycol=1653;

FROMBIC	RLTDFROMBIC
CRESUS33	LOYDGB21002

All three return

db<>fiddle

---

If you're still on a version before 12c then you can use cross join instead of cross apply:

select x.fromBIC, x.RltdFromBIC
from xml_key t
cross join XMLTable(
  '/AppHdr'
  passing XMLTYPE(t.xml)
  columns
    fromBIC path 'Fr/FIId/FinInstnId/BICFI',
    RltdFromBIC path 'Rltd/Fr/FIId/FinInstnId/BICFI'
) x
where mycol=1653;

They aren't exactly the same but shouldn't make a difference here.

db<>fiddle