Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table paymentinterchange (transportdata blob);
insert into paymentinterchange (transportdata)
values (utl_raw.cast_to_raw('<?xml version="1.0" encoding="ISO-8859-1"?>
<Document xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03 pain.001.001.03.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03" >
 <CstmrCdtTrfInitn>
  <GrpHdr>
   <MsgId>SEPA21012022</MsgId>
   <CreDtTm>2022-01-20T11:45:59.177</CreDtTm>
   <NbOfTxs>12</NbOfTxs>
   <CtrlSum>22277.57</CtrlSum>
   <InitgPty/>
   <Nm/>
  </GrpHdr>
 </CstmrCdtTrfInitn>
</Document>'));
1 rows affected

SELECT XMLtype(transportdata,1).getStringVal() from paymentinterchange


XMLTYPE(TRANSPORTDATA,1).GETSTRINGVAL()


<?xml version="1.0" encoding="UTF-8"?>
<Document xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03 pain.001.001.03.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03">
  <CstmrCdtTrfInitn>
    <GrpHdr>
      <MsgId>SEPA21012022</MsgId>
      <CreDtTm>2022-01-20T11:45:59.177</CreDtTm>
      <NbOfTxs>12</NbOfTxs>
      <CtrlSum>22277.57</CtrlSum>
      <InitgPty/>
      <Nm/>
    </GrpHdr>
  </CstmrCdtTrfInitn>
</Document>



with q1 (Tdata) as (
  SELECT XMLtype(transportdata,1) from paymentinterchange
)
select EXTRACT(q1.Tdata, '/Document/CstmrCdtTrfInitn/GrpHdr/MsgId')
from q1


EXTRACT(Q1.TDATA,'/DOCUMENT/CSTMRCDTTRFINITN/GRPHDR/MSGID')


null


with q1 (Tdata) as (
  SELECT XMLtype(transportdata,1) from paymentinterchange
)
select EXTRACT(q1.Tdata, '/Document/CstmrCdtTrfInitn/GrpHdr/MsgId', 'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"')
from q1


EXTRACT(Q1.TDATA,'/DOCUMENT/CSTMRCDTTRFINITN/GRPHDR/MSGID','XMLNS="URN:ISO:STD:ISO:20022:TECH:XSD:PAIN.001.001.03"')


<MsgId xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03">SEPA21012022</MsgId>


with q1 (Tdata) as (
  SELECT XMLtype(transportdata,1) from paymentinterchange
)
select EXTRACT(q1.Tdata, '/Document/CstmrCdtTrfInitn/GrpHdr/MsgId/text()', 'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"')
from q1


EXTRACT(Q1.TDATA,'/DOCUMENT/CSTMRCDTTRFINITN/GRPHDR/MSGID/TEXT()','XMLNS="URN:ISO:STD:ISO:20022:TECH:XSD:PAIN.001.001.03"')


SEPA21012022


with q1 (Tdata) as (
  SELECT XMLtype(transportdata,1) from paymentinterchange
)
select XMLQuery('
    declare default element namespace "urn:iso:std:iso:20022:tech:xsd:pain.001.001.03";
    /Document/CstmrCdtTrfInitn/GrpHdr/MsgId/text()'
  passing Tdata
  returning content).getstringval()
from q1


XMLQUERY('DECLAREDEFAULTELEMENTNAMESPACE"URN:ISO:STD:ISO:20022:TECH:XSD:PAIN.001.001.03";/DOCUMENT/CSTMRCDTTRFINITN/GRPHDR/MSGID/TEXT()'PASSINGTDATARETURNINGCONTENT).GETSTRINGVAL()


SEPA21012022