Hello Friends,
I am trying the get the aggregate values from multiple blocks in a nested xml.
Below is the block.
<Store ID="1234" xmlns="http://abc.com/txn">
<Location Name="xyz">
<Transactions>
<Transaction>
<Type>Credit</Type>
<Name>Batman</Name>
<Amount>10</Amount>
<Date>2010-03-17</Date>
</Transaction>
<Transaction>
<Type>Credit</Type>
<Name>Joker</Name>
<Amount>20</Amount>
<Date>2010-03-17</Date>
</Transaction>
</Transactions>
<Details>
<Owner>Superman<Owner>
<State>Krypton</State>
</Details>
</Location>
</Store>
Expected Output :
Batman;Joker
30
All I could think of is below, but I believe there's a better way to write this .. maybe using XMLAGG ??
Appreciate if you could help.
select sd.store_id, xml_tab2.nm
from store_details sd
JOIN XMLTABLE
(
XMLNAMESPACES(DEFAULT 'http://abc.com/txn'),
'(/)'
PASSING sd.store_xml COLUMNS
store_id VARCHAR2(20 CHAR) PATH '@ID',
loc VARCHAR2(20 CHAR) PATH 'Store/Location/@Name',
txn_xml xmltype PATH 'Store/Location/Transactions'
) xml_tab1
ON (1=1)
join xmltable (XMLNAMESPACES(DEFAULT 'http://abc.com/txn'),
'(/Transactions/Transaction)'
PASSING xml_tab1.txn_xml
COLUMNS
nm varchar2(200) PATH 'Name'
) xml_tab2 on (1=1)