Hi
I am attempting to count the number of rows that have a "DEAL_OBJECT" tag , so I used the existsnode function as below (over 3 sample xml's , 2 of which have DEAL_OBJECT), and I was expecting a result of 2, but 0 is returned.
with t as (
select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
<DEAL_OBJECT xmlns="http://www.xyz.com/firc/">
<INSTRUMENT_LINK_Association>
<SWAPTION>
<SWAP>
<PRODUCT_FLOW_LIST>
<PRODUCT_FLOW>
<FLOW_RATESPREAD>0.04875</FLOW_RATESPREAD>
<FLOW_FACE>1.4219777502E8</FLOW_FACE>
</PRODUCT_FLOW>
<PRODUCT_FLOW>
<FLOW_RATESPREAD>0.04875</FLOW_RATESPREAD>
<FLOW_FACE>1.4912991655E8</FLOW_FACE>
</PRODUCT_FLOW>
</PRODUCT_FLOW_LIST>
</SWAP>
</SWAPTION>
</INSTRUMENT_LINK_Association>
</DEAL_OBJECT>') as msg from dual union all
select xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
<DEAL_OBJECT xmlns="http://www.xyz.com/firc/">
<INSTRUMENT_LINK_Association>
<SWAPTION>
<SWAP>
<CC_QUANTITY>10266</CC_QUANTITY>
</SWAP>
</SWAPTION>
</INSTRUMENT_LINK_Association>
</DEAL_OBJECT>') from dual union all
select xmltype('<?xml version="1.0" encoding="UTF-8"?>
<Portfolio><Query/><Trade>
<DealIdentity>
<DEAL_TICKETNUM>10473153</DEAL_TICKETNUM>
</DealIdentity>
<DealFinancialInformation>
<CollateralType>USD_GOVT</CollateralType>
<RazorTrade TradeName="XHYP_10473153_1">
<TradeCurrencies>
<ValueCurrency>JPY</ValueCurrency>
</TradeCurrencies>
<Outline name="GeneralFloater" version="4_5"/>
<InsertParameter name="NOTIONAL">1100000000</InsertParameter>
<InsertParameter name="FUNDNOTIONAL">9061000</InsertParameter>
</RazorTrade>
</DealFinancialInformation>
</Trade></Portfolio>') from dual
)
--
select count(*)
from t
where existsnode (t.msg, '/DEAL_OBJECT', 'xmlns:who="http://www.xyz.com/firc/"') = 1;
Any ideas how I can modify the exists ?