Thank you for the above assistance.
is this the correct way for me to extract my values, when there are multiple iterations in different elememts. I get the answer that I can use and I expect. But is the plan "healthy" and the method correct? My eybrows arose at the the high cost and card.
My XML snippet ..
<SwInt:RequestHeader>
<SwInt:Requestor>cn=john-smith,ou=ny,o=bankdcba,o=swift</SwInt:Requestor>
<SwInt:Responder>cn=mnop31,ou=zurich,o=bankwxyz,o=swift</SwInt:Responder>
<SwInt:Service>swift.ifds!p</SwInt:Service>
<SwInt:RequestRef>dcba-02241-235851-000972</SwInt:RequestRef>
</SwInt:RequestHeader>
<SwInt:RequestPayload>
<Doc:Document xmlns:xsi="</code>
http://www.w3.org/2001/XMLSchema-instance" <code>xmlns:Doc="urn:swift:xsd:swift.cashrepv1$getaccount">
<Doc:getaccount>
<Doc:NstrAcctSchCrit>
<Doc:AcctId>
<Doc:DmstAcct>ACCOUNT1</Doc:DmstAcct>
<Doc:DmstAcct>ACCOUNT2</Doc:DmstAcct>
</Doc:AcctId>
<Doc:Ccy>GBP</Doc:Ccy>
<Doc:Ccy>USD</Doc:Ccy>
<Doc:Ccy>AUD</Doc:Ccy>
</Doc:NstrAcctSchCrit>
</Doc:getaccount>
</SwInt:RequestPayload>
...
select
(substr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Responder/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),
instr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Responder/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),'o=',-1,2)+2,
instr(substr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Responder/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),
instr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Responder/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),'o=',-1,2)+2),',')-1)
)
Servicer,
(substr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Requestor/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),
instr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Requestor/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),'o=',-1,2)+2,
instr(substr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Requestor/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),
instr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Requestor/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),'o=',-1,2)+2),',')-1)
)
Owner ,
extract(value(Acc), '//Doc:DmstAcct/text()','xmlns:Doc="urn:swift:xsd:swift.cashrepv1$getaccount"').getStringVal() Accout,
extract(value(Cur), '//Doc:Ccy/text()','xmlns:Doc="urn:swift:xsd:swift.cashrepv1$getaccount"').getStringVal() Currency
from n_sys.xml1,
table( xmlsequence( extract(swift_xml, '//Doc:AcctId/Doc:DmstAcct','xmlns:Doc="urn:swift:xsd:swift.cashrepv1$getaccount"') )) Acc,
table( xmlsequence( extract(swift_xml, '//Doc:NstrAcctSchCrit/Doc:Ccy','xmlns:Doc="urn:swift:xsd:swift.cashrepv1$getaccount"') )) Cur
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=440398966 Card=32697
6213824 Bytes=655914284930944)
1 0 NESTED LOOPS (Cost=440398966 Card=326976213824 Bytes=65591
4284930944)
2 1 NESTED LOOPS (Cost=53918 Card=40031368 Bytes=80222861472
)
3 2 TABLE ACCESS (FULL) OF 'XML1' (Cost=7 Card=4901 Bytes=
9811802)
4 2 COLLECTION ITERATOR (PICKLER FETCH) OF 'XMLSEQUENCEFRO
MXMLTYPE'
5 1 COLLECTION ITERATOR (PICKLER FETCH) OF 'XMLSEQUENCEFROMX
MLTYPE'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
646 bytes sent via SQL*Net to client
605 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
PS .... excellent effort by Oracle with reference/use of XML! !