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