I have a requirement to extract some xml embedded within an xml message that I read off a topic.
A script that attempts to extract a sample (much reduced) payload appears as:
set serveroutput on
declare
l_xml xmltype := xmltype('<?xml version="1.0" encoding="ISO-8859-1"?>
<DEAL_OBJECT xmlns="http://www.xyz.com/firc/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.xyz.com/firc/schemas/XSD/DEAL_OBJECT.xsd">
<DEAL_GENERATION_STATE>
<DEAL_GENERATION_MAP_Entry>
<DEAL_GENERATION_MAP_Key>0</DEAL_GENERATION_MAP_Key>
<DEAL_GENERATION_MAP_Value>Undefined</DEAL_GENERATION_MAP_Value>
</DEAL_GENERATION_MAP_Entry>
</DEAL_GENERATION_STATE>
<PARAMETRICXML_OBJECT>
<PARAMETRICXML_PARAMETERS><![CDATA[<?xml version="1.0">
<Portfolio>
<Query/>
<Trade>
<NotionalInformation>
<FixedNotional>250000</FixedNotional>
<MarginInformation>0.18</MarginInformation>
</NotionalInformation>
<NotionalInformation>
<FixedNotional>250000</FixedNotional>
<MarginInformation>-0.0016</MarginInformation>
</NotionalInformation>
</Trade>
</Portfolio>]]>
</PARAMETRICXML_PARAMETERS>
</PARAMETRICXML_OBJECT>
</DEAL_OBJECT>');
l_cdata varchar2(32747);
begin
select x.parametric_param_xml into l_cdata
from (select l_xml as m from dual) d
,xmltable('/DEAL_OBJECT' passing d.m
columns parametric_param_xml varchar2(4000) path '//PARAMETRICXML_PARAMETERS') as x;
dbms_output.put_line('PARAMETRICXML_PARAMETERS: ' || l_cdata);
end;
/
Furthermore, what I require is the first occurrence of the <FixedNotional> value, since there can be multiple such tags.
Is this at all possible?
At the moment, I cannot even seem to output the CDATA text.
I am using 11gR2.