This is my oracle SQL :

WITH q1(Tdata,paymentinterchangekey) AS
(
  SELECT XMLtype(transportdata, 1), paymentinterchangekey
    FROM bph_owner.paymentinterchange
   WHERE paymentinterchangekey = '137630105'
)
SELECT EXTRACT(q1.Tdata, '/Document/CstmrCdtTrfInitn/GrpHdr/MsgId'),
       q1.Tdata,
       q1.paymentinterchangekey "EE",
       q1.paymentinterchangekey "EE"
  FROM q1;

and data is :

<?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>

Any idea why the EXTRACT value is empty?

CodePudding user response：

EXTRACT is deprecated. Use XMLQUERY and declare the namespace:

with q1 ( Tdata, paymentinterchangekey) AS (
  SELECT XMLtype(transportdata),
         paymentinterchangekey 
  from   bph_owner.paymentinterchange 
  where  paymentinterchangekey = '137630105'
)
select XMLQUERY(
         'declare default element namespace "urn:iso:std:iso:20022:tech:xsd:pain.001.001.03";
         /Document/CstmrCdtTrfInitn/GrpHdr/MsgId/text()'
         PASSING q1.Tdata
         RETURNING CONTENT
       ) AS msgid,
       q1.Tdata,
       q1.paymentinterchangekey "EE" 
from   q1;

db<>fiddle here

CodePudding user response：

Ignoring the malformed XML, the issue is that your Document node declares a namespace, which you are not including; so you would need to do:

EXTRACT(q1.Tdata, '/Document/CstmrCdtTrfInitn/GrpHdr/MsgId',
  'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"')

which would give you

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

Presumably you only want the actual value, not the whole node, so you could do:

EXTRACT(q1.Tdata, '/Document/CstmrCdtTrfInitn/GrpHdr/MsgId/text()',
  'xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03"')

to get

SEPA21012022

However, the EXTRACT() function is deprecated, and has been for a long time, so you should use XMLQuery instead:

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

db<>fiddle

If you want multiple values from the same XML then look at XMLTable.

So I have now run this as a generic SQL but get the following error : ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00216: invalid character 195 (0xC3) Error at line 150 ORA-06512: at "SYS.XMLTYPE", line 283 ORA-06512: at line 1 Is there a way to 'skip' malformed rows?

The rows aren't malformed. The XML declaration says the encoding is ISO-8859-1, which allows any 'Latin-1 (Western)' characters, such as Ã - which is encoded as 0xC3. Your XML is stored as a BLOB, which you are converting to XMLType. But as part of that conversion you are passing 1 as the csid value. That represents ASCII (or US7ASCII in Oracle). So the conversion is only expecting ASCII characters, and is complaining about anything else.

The Oracle character set name corresponding to ISO-8859-1 is WE8ISO8859P1, so you need to supply the character set ID for that instead:

with q1 (Tdata) as (
  SELECT XMLtype(transportdata, nls_charset_id('WE8ISO8859P1')) from paymentinterchange
)
...

db<>fiddle showing the same error you are getting and the successful query with the correct character set.