-
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got
Hi Expert Developers
Can you please help me in modifying the query to fix ORA-19279: error message .
below queries works for 2nd row but does not work for 1st row .
select X.*
from abc evt
,XMLTABLE (XMLNAMESPACES ('urn:swift:xsd:mtmsg.2011' AS NS2, DEFAULT 'urn:swift:xsd:fin.970.2011'),
'$msg_xml' PASSING EVT.col1 as "msg_xml"
COLUMNS
F61ValueDate Varchar(40) Path '/F61/ValueDate[1]',
DebitCreditMark Varchar(40) Path '//Document/MT970/F61a/F61/DebitCreditMark[1]',
Amount Varchar(40) Path '//Document/MT970/F61a/F61/Amount[1]',
TransactionType Varchar(40) Path '//Document/MT970/F61a/F61/TransactionType[1]',
IdentificationCode Varchar(40) Path '//Document/MT970/F61a/F61/IdentificationCode[1]',
ReferenceForTheAccountOwner Varchar(40) Path '//Document/MT970/F61a/F61/ReferenceForTheAccountOwner[1]',
SupplementaryDetails Varchar(40) Path '//Document/MT970/F61a/F61/SupplementaryDetails[1]'
) X
where EVT.col2 = 1
--------
CREATE TABLE "SYS"."ABC"
(
"COL1" "SYS"."XMLTYPE" ,
"COL2" NUMBER
);
2 XML's
F
01
IRVTDEFXAXXX
5252
699163
O
970
1633
131101
EBASBEBBQ
XXX
1113
070016
131101
N
ENS0000857017566
ocument="urn:swift:xsd:fin.970.2011">
ENS17566/FINAL
IRVTDEFX/EUR/131101/N
215
16
D
131101
EUR
2686836,28
131101
D
40248,
S
103
FX5445414
KREDBEBBXXXIRVTDEFXXXXN011031
131101
D
41605,
S
103
FX5443846
DEUTDEFFXXXIRVTDEFXXXXN011031
131101
D
49197,9
S
103
OPRRFPAJG192A
BREDFRPPXXXIRVTDEFXXXXN011033
131101
D
50000,
S
103
FX5443921
BARCGB22XXXIRVTDEFXXXXN011031
D
131101
EUR
3736765,9
-------------
F
01
IRVTDEFXAXXX
5252
699163
O
970
1633
131101
EBASBEBBQ
XXX
1113
070016
131101
N
ENS0000857017566
ocument="urn:swift:xsd:fin.970.2011">
ENS17566/FINAL
IRVTDEFX/EUR/131101/N
215
16
D
131101
EUR
2686836,28
131101
D
40248,
S
103
FX5445414
KREDBEBBXXXIRVTDEFXXXXN011031
D
131101
EUR
3736765,9
siva prakash
DBA
-
prakashs43@yahoo.com
i can send xml attachments
siva prakash
DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|