11g 11.2.0.3.0
Newbie on XMLTABLE XML DB!
sample XML (person_data.person_data):
<CstmrPmtStsRpt>
<GrpHdr>
<MsgId>99</MsgId>
<CreDtTm>2013-11-12T08:10:57</CreDtTm>
<InitgPty>
<Id>
<OrgId>
<Othr>
<Id>BANK_ID</Id>
<SchmeNm>
<Cd>BANK</Cd>
</SchmeNm>
</Othr>
<Othr>
<Id>CUST_ID</Id>
<SchmeNm>
<Cd>CUST</Cd>
</SchmeNm>
</Othr>
</OrgId>
</Id>
</InitgPty>
</GrpHdr>
</CstmrPmtStsRpt>
Would like to extract the Id associated with the BANK (and only the bank Id):
Requested SQL output:
BANK_ID
So far I've come up with:
SELECT t.column_value
FROM person_data a,
XMLTABLE (
'for $root in /CstmrPmtStsRpt/GrpHdr/InitgPty/Id/OrgId/Othr
where $root/SchmeNm/Cd = "BANK"
return $root/Id'
passing a.person_data) t;
Which returns
<Id>BANK_ID</Id>
Adding text() as below:
SELECT t.column_value
FROM person_data a,
XMLTABLE (
'for $root in /CstmrPmtStsRpt/GrpHdr/InitgPty/Id/OrgId/Othr
where $root/SchmeNm/Cd = "BANK"
return $root/Id/text()'
passing a.person_data) t;
Returns <no rows returned>
I know I'm missing something fundamental here - just need a pointer. Thanks!