Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE paymentinterchange (
  transportdata,
  paymentinterchangekey,
  transporttime,
  fileformat
) AS
SELECT EMPTY_CLOB() || '<Id xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"><PrvtId><Othr><Id>IE57ZZZ05327119</Id><SchmeNm><Prtry>SEPA</Prtry></SchmeNm></Othr></PrvtId></Id>',
       1,
       DATE '2024-01-11',
       'paint'
FROM   DUAL UNION ALL
SELECT EMPTY_CLOB() || '<FinInstnId xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"/>',
       2,
       DATE '2024-01-11',
       'paint'
FROM   DUAL;
2 rows affected

SELECT  C1.column_value,
        p.paymentinterchangekey AS Key
FROM    /*bph_owner.*/paymentinterchange p
        CROSS JOIN XMLTABLE(
          '//*'
           PASSING XMLtype(p.transportdata)
        ) C1
WHERE   p.TRANSPORTTIME >= DATE '2024-01-11'
AND     p.TRANSPORTTIME <  DATE '2024-04-12'
AND     LENGTH(p.transportdata)>0
AND     p.FILEFORMAT like 'pain%';


COLUMN_VALUE
KEY


<Id xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"><PrvtId><Othr><Id>IE57ZZZ05327119</Id><SchmeNm><Prtry>SEPA</Prtry></SchmeNm></Othr></PrvtId></Id>
1

<PrvtId xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"><Othr><Id>IE57ZZZ05327119</Id><SchmeNm><Prtry>SEPA</Prtry></SchmeNm></Othr></PrvtId>
1

<Othr xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"><Id>IE57ZZZ05327119</Id><SchmeNm><Prtry>SEPA</Prtry></SchmeNm></Othr>
1

<Id xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02">IE57ZZZ05327119</Id>
1

<SchmeNm xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"><Prtry>SEPA</Prtry></SchmeNm>
1

<Prtry xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02">SEPA</Prtry>
1

<FinInstnId xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"/>
2


SELECT  C1.column_value,
        p.paymentinterchangekey AS Key
FROM    /*bph_owner.*/paymentinterchange p
        CROSS JOIN XMLTABLE(
          '//*[not(*)][not(text())]'
           PASSING XMLtype(p.transportdata)
        ) C1
WHERE   p.TRANSPORTTIME >= DATE '2024-01-11'
AND     p.TRANSPORTTIME <  DATE '2024-04-12'
AND     LENGTH(p.transportdata)>0
AND     p.FILEFORMAT like 'pain%';


COLUMN_VALUE
KEY


<FinInstnId xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"/>
2

COLUMN_VALUE	KEY
<Id xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"><PrvtId><Othr><Id>IE57ZZZ05327119</Id><SchmeNm><Prtry>SEPA</Prtry></SchmeNm></Othr></PrvtId></Id>	1
<PrvtId xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"><Othr><Id>IE57ZZZ05327119</Id><SchmeNm><Prtry>SEPA</Prtry></SchmeNm></Othr></PrvtId>	1
<Othr xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"><Id>IE57ZZZ05327119</Id><SchmeNm><Prtry>SEPA</Prtry></SchmeNm></Othr>	1
<Id xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02">IE57ZZZ05327119</Id>	1
<SchmeNm xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"><Prtry>SEPA</Prtry></SchmeNm>	1
<Prtry xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02">SEPA</Prtry>	1
<FinInstnId xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"/>	2