XMLCast,XMLQuery vs extractValue
942880Jun 20 2012 — edited Jun 27 2012Hello fellows, newbie question -- I'm unable to find a working example to extract values using the XMLCast/XMLQuery in place of the deprecated extractValue
Here is an XMLTYPE containing
<CalculateFITResponse xmlns="http://tempuri.org/">
<CalculateFITResult xmlns:a="http://schemas.datacontract.org/2004/07/ValueNavigatorService.Classes" xmlns:i="http://www.
w3.org/2001/XMLSchema-instance">
<a:VnSegments/>
<a:declineRate>0</a:declineRate>
<a:entityId>128041676</a:entityId>
<a:eur>0</a:eur>
<a:historyVolume>60000</a:historyVolume>
<a:results>FitSuccessful</a:results>
<a:segmentData xmlns:b="http://schemas.datacontract.org/2004/07/Eni.ValueNavigator.DeclineFitting"/>
<a:segmentDataList xmlns:b="http://schemas.datacontract.org/2004/07/Eni.ValueNavigator.DeclineFitting"/>
<a:segmentVolume>0</a:segmentVolume>
</CalculateFITResult>
</CalculateFITResponse>
This function -
CREATE OR REPLACE FUNCTION get_EUR_V_Results (CalculateFIT_OutputMessage IN XMLTYPE) RETURN VARCHAR2
IS
V_Result VARCHAR2(4000) := NULL;
l_ns_1 VARCHAR2(4000) := ' xmlns:a="http://schemas.datacontract.org/2004/07/ValueNavigatorService.Classes"';
l_ns_2 VARCHAR2(4000) := ' xmlns:i="http://www.w3.org/2001/XMLSchema-instance"';
l_ns_3 VARCHAR2(4000) := ' xmlns="http://tempuri.org/"';
l_ns VARCHAR2(4000) := l_ns_1||l_ns_2||l_ns_3;
l_xpath VARCHAR2(4000) := '//CalculateFITResponse//CalculateFITResult/a:';
BEGIN
SELECT extractValue(CalculateFIT_OutputMessage, l_xpath||'results', l_ns) INTO V_Result from dual;
RETURN V_Result;
END;
/
returns FitSuccessful
(sucessfully I might add)
How do I rewrite the function using XMLCast and XMLQuery?
(Yes, I have RTFM with no headway)
Thanks in advance,
HKT