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

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>SEPA21012023</MsgId>
   <CreDtTm>2022-01-20T11:45:59.177</CreDtTm>
   <NbOfTxs>12</NbOfTxs>
   <CtrlSum>22277.57</CtrlSum>
   <InitgPty>Ã</InitgPty>
   <Nm/>
  </GrpHdr>
 </CstmrCdtTrfInitn>
</Document>'));
1 rows affected

with q1 (Tdata) as (
  SELECT XMLtype(transportdata, nls_charset_id('WE8ISO8859P1')) 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

SEPA21012023


insert into paymentinterchange (transportdata)
values (utl_raw.cast_to_raw('<?xml version="1.0" encoding="UTF-8"?>
<S2SCTScf:SCTScfBlkCredTrf
    xsi:schemaLocation="urn:S2SCTScf:xsd:$SCTScfBlkCredTrf SCTScfBlkCredTrf.xsd"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:S2SCTScf="urn:S2SCTScf:xsd:$SCTScfBlkCredTrf">
  <S2SCTScf:SndgInst>EBAPFRPA</S2SCTScf:SndgInst>
  <S2SCTScf:RcvgInst>NWBKGB2X</S2SCTScf:RcvgInst>
  <S2SCTScf:SrvcId>SCT</S2SCTScf:SrvcId>
  <S2SCTScf:TstCode>P</S2SCTScf:TstCode>
  <S2SCTScf:FType>SCF</S2SCTScf:FType>
  <S2SCTScf:FileRef>NSCT220610000729</S2SCTScf:FileRef>
  <S2SCTScf:RoutingInd>IND</S2SCTScf:RoutingInd>
  <S2SCTScf:FileBusDt>2022-06-10</S2SCTScf:FileBusDt>
  <S2SCTScf:FileCycleNo>11</S2SCTScf:FileCycleNo>
  <S2SCTScf:FIToFICstmrCdtTrf
      xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.02">
    <GrpHdr>
      <MsgId>NSCT2206100007290000000000000000001</MsgId>
      <CreDtTm>2022-06-10T09:52:54</CreDtTm>
    </GrpHdr>
  </S2SCTScf:FIToFICstmrCdtTrf>
</S2SCTScf:SCTScfBlkCredTrf>'));
1 rows affected

with q1 (Tdata) as (
  SELECT XMLtype(transportdata, nls_charset_id('WE8ISO8859P1')) 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

SEPA21012023

null


with q1 (Tdata) as (
  SELECT XMLtype(transportdata, nls_charset_id('AL32UTF8')) from paymentinterchange
)
select XMLQuery('//*:GrpHdr/*:MsgId/text()'
  passing Tdata
  returning content).getstringval()
from q1


XMLQUERY('//*:GRPHDR/*:MSGID/TEXT()'PASSINGTDATARETURNINGCONTENT).GETSTRINGVAL()


SEPA21012022

SEPA21012023

NSCT2206100007290000000000000000001