Hi
I am attempting to extract the max FLOW_FACE from the sample xml given below. I appreciate that it would be a simple case of creating two columns, one for
//PRODUCT_FLOW[1]/FLOW_FACE and another for //PRODUCT_FLOW[2]/FLOW_FACE
and then obtaining the greatest() between the two, or if there was even three.
However I do not know how many such PRODUCT_FLOW's there will exist.
Furthermore, "PRODUCT_FLOW_LIST", may or may not occur with the larger xml.
with xml_data 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>') as msg from dual
)
--
select x.cc_quantity --, x.max_flow_face
from xml_data d
,xmltable (xmlnamespaces(default 'http://www.xyz.com/firc/')
,'/DEAL_OBJECT' passing d.msg
columns
--max_flow_face varchar2(100)
-- path '/SWAP/let $values := for $x in ( $p/PRODUCT_FLOW/FLOW_FACE ) return xs:integer($x) return fn:max($values)' )
-- path '/SWAP/fn:max(/PRODUCT_FLOW/FLOW_FACE)' ,
cc_quantity varchar2(100) path 'INSTRUMENT_LINK_Association/SWAPTION/SWAP/CC_QUANTITY'
) as x;
So the above are 2 messages, 1 of which does not have a PRODUCT_FLOW_LIST .
How can I get the max in this case using xpath or maybe combined with xquery ?
I found this info on max, https://docs.oracle.com/cd/E13214_01/wli/docs92/xref/xqaggrfunc.html#wp1144472 , which I am unsure how to include in the above query.