Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Read XML inside a CDATA

Mettemusens2Mar 26 2012 — edited Apr 25 2012
Hi there

I have to call a web service from PL/SQL and parse the returned values

I have a return call like this:
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <GetDefinitionResponse xmlns="http://synergi.com">
         <GetDefinitionResult><![CDATA[<?xml version="1.0" encoding="utf-8"?>
<Favourite>
  <PkFavourite>61057</PkFavourite>
  <ParentFavourite></ParentFavourite>
  <Type>1</Type>
  <Report>6</Report>
  <AutoExecute>1</AutoExecute>
  <SeparateCondition>0</SeparateCondition>
  <ExposurePercentInc></ExposurePercentInc>
  <ExposureFactor></ExposureFactor>
  <ExposureApplication></ExposureApplication>
  <Disabled></Disabled>
  <ExpireDate></ExpireDate>
  <Language>
    <PkFavourite>61057</PkFavourite>
    <Language>1</Language>
    <Description>id report1</Description>
    <Url></Url>
  </Language>
  <Language>
    <PkFavourite>61057</PkFavourite>
    <Language>2</Language>
    <Description>id report1</Description>
    <Url></Url>
  </Language>
  <Language>
    <PkFavourite>61057</PkFavourite>
    <Language>3</Language>
    <Description>id report1</Description>
    <Url></Url>
  </Language>
  <Language>
    <PkFavourite>61057</PkFavourite>
    <Language>4</Language>
    <Description>id report1</Description>
    <Url></Url>
  </Language>
  <Condition>
    <PkFavourite>61057</PkFavourite>
    <ConditionNo>3</ConditionNo>
    <DbFunctionDomain>0</DbFunctionDomain>
    <Domain>107</Domain>
    <ValueListOperator>2</ValueListOperator>
    <ConditionOperator>1</ConditionOperator>
    <ConditionOperator2>0</ConditionOperator2>
    <ExtraSqlCondition></ExtraSqlCondition>
    <FlgExactLevel>0</FlgExactLevel>
    <FlgForceExists>0</FlgForceExists>
    <ConditionValue>
      <PkFavourite>61057</PkFavourite>
      <ConditionNo>3</ConditionNo>
      <ConditionValueNo>1</ConditionValueNo>
      <DbFunctionValue>0</DbFunctionValue>
      <ValueOperator>0</ValueOperator>
      <NumberValue></NumberValue>
      <StringValue></StringValue>
      <DateValue></DateValue>
      <NumberValue2></NumberValue2>
      <PeriodPredef>24</PeriodPredef>
    </ConditionValue>
    <ConditionDomain>
      <PkFavourite>61057</PkFavourite>
      <ConditionNo>3</ConditionNo>
      <Domain>540</Domain>
    </ConditionDomain>
  </Condition>
  <Condition>
    <PkFavourite>61057</PkFavourite>
    <ConditionNo>2</ConditionNo>
    <DbFunctionDomain>0</DbFunctionDomain>
    <Domain>531</Domain>
    <ValueListOperator>2</ValueListOperator>
    <ConditionOperator>0</ConditionOperator>
    <ConditionOperator2>0</ConditionOperator2>
    <ExtraSqlCondition></ExtraSqlCondition>
    <FlgExactLevel>0</FlgExactLevel>
    <FlgForceExists>0</FlgForceExists>
    <ConditionValue>
      <PkFavourite>61057</PkFavourite>
      <ConditionNo>2</ConditionNo>
      <ConditionValueNo>1</ConditionValueNo>
      <DbFunctionValue>0</DbFunctionValue>
      <ValueOperator>14</ValueOperator>
      <NumberValue>3</NumberValue>
      <StringValue></StringValue>
      <DateValue></DateValue>
      <NumberValue2></NumberValue2>
      <PeriodPredef>0</PeriodPredef>
    </ConditionValue>
    <ConditionDomain>
      <PkFavourite>61057</PkFavourite>
      <ConditionNo>2</ConditionNo>
      <Domain>531</Domain>
    </ConditionDomain>
  </Condition>
  <Condition>
    <PkFavourite>61057</PkFavourite>
    <ConditionNo>4</ConditionNo>
    <DbFunctionDomain>0</DbFunctionDomain>
    <Domain>178</Domain>
    <ValueListOperator>2</ValueListOperator>
    <ConditionOperator>1</ConditionOperator>
    <ConditionOperator2>0</ConditionOperator2>
    <ExtraSqlCondition></ExtraSqlCondition>
    <FlgExactLevel>0</FlgExactLevel>
    <FlgForceExists>0</FlgForceExists>
    <ConditionValue>
      <PkFavourite>61057</PkFavourite>
      <ConditionNo>4</ConditionNo>
      <ConditionValueNo>1</ConditionValueNo>
      <DbFunctionValue>0</DbFunctionValue>
      <ValueOperator>14</ValueOperator>
      <NumberValue>50021</NumberValue>
      <StringValue></StringValue>
      <DateValue></DateValue>
      <NumberValue2></NumberValue2>
      <PeriodPredef>0</PeriodPredef>
    </ConditionValue>
    <ConditionValue>
      <PkFavourite>61057</PkFavourite>
      <ConditionNo>4</ConditionNo>
      <ConditionValueNo>2</ConditionValueNo>
      <DbFunctionValue>0</DbFunctionValue>
      <ValueOperator>14</ValueOperator>
      <NumberValue>50019</NumberValue>
      <StringValue></StringValue>
      <DateValue></DateValue>
      <NumberValue2></NumberValue2>
      <PeriodPredef>0</PeriodPredef>
    </ConditionValue>
    <ConditionValue>
      <PkFavourite>61057</PkFavourite>
      <ConditionNo>4</ConditionNo>
      <ConditionValueNo>3</ConditionValueNo>
      <DbFunctionValue>0</DbFunctionValue>
      <ValueOperator>14</ValueOperator>
      <NumberValue>50018</NumberValue>
      <StringValue></StringValue>
      <DateValue></DateValue>
      <NumberValue2></NumberValue2>
      <PeriodPredef>0</PeriodPredef>
    </ConditionValue>
    <ConditionValue>
      <PkFavourite>61057</PkFavourite>
      <ConditionNo>4</ConditionNo>
      <ConditionValueNo>4</ConditionValueNo>
      <DbFunctionValue>0</DbFunctionValue>
      <ValueOperator>14</ValueOperator>
      <NumberValue>50020</NumberValue>
      <StringValue></StringValue>
      <DateValue></DateValue>
      <NumberValue2></NumberValue2>
      <PeriodPredef>0</PeriodPredef>
    </ConditionValue>
    <ConditionDomain>
      <PkFavourite>61057</PkFavourite>
      <ConditionNo>4</ConditionNo>
      <Domain>540</Domain>
    </ConditionDomain>
  </Condition>
  <Connected>
    <PkFavourite>61057</PkFavourite>
    <Connected>3</Connected>
    <Operator>0</Operator>
  </Connected>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>2650</DomainNode>
    <Report>2</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>2435</DomainNode>
    <Report>2</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>110</DomainNode>
    <Report>3</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>116</DomainNode>
    <Report>3</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>2006</DomainNode>
    <Report>3</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>2005</DomainNode>
    <Report>3</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>2015</DomainNode>
    <Report>3</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>110</DomainNode>
    <Report>4</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>350</DomainNode>
    <Report>4</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>2650</DomainNode>
    <Report>4</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>2435</DomainNode>
    <Report>4</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>2482</DomainNode>
    <Report>5</Report>
  </Option>
  <Option>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>116</DomainNode>
    <Report>1</Report>
  </Option>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>107</DomainNode>
    <Sequence>0</Sequence>
    <Report>2</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>357</DomainNode>
    <Sequence>1</Sequence>
    <Report>2</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>390</DomainNode>
    <Sequence>2</Sequence>
    <Report>2</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>107</DomainNode>
    <Sequence>0</Sequence>
    <Report>3</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>357</DomainNode>
    <Sequence>1</Sequence>
    <Report>3</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>107</DomainNode>
    <Sequence>0</Sequence>
    <Report>4</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>357</DomainNode>
    <Sequence>1</Sequence>
    <Report>4</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>390</DomainNode>
    <Sequence>2</Sequence>
    <Report>4</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>357</DomainNode>
    <Sequence>0</Sequence>
    <Report>5</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>357</DomainNode>
    <Sequence>0</Sequence>
    <Report>12</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>107</DomainNode>
    <Sequence>1</Sequence>
    <Report>1</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <Sort>
    <PkFavourite>61057</PkFavourite>
    <DomainNode>357</DomainNode>
    <Sequence>2</Sequence>
    <Report>1</Report>
    <SortOrderType>1</SortOrderType>
  </Sort>
  <FavouriteReport>
    <PkFavourite>61057</PkFavourite>
    <Report>6</Report>
    <SelectionAggrType>1</SelectionAggrType>
    <SelectionAggrDomain>3112</SelectionAggrDomain>
    <TargetApplication></TargetApplication>
    <SpecialReport></SpecialReport>
    <PredefDate></PredefDate>
    <RollingAveragePeriods>0</RollingAveragePeriods>
  </FavouriteReport>
  <ReportType>
    <PkFavourite>61057</PkFavourite>
    <Report>6</Report>
    <ReportType>1</ReportType>
  </ReportType>
  <Selection>
    <PkFavourite>61057</PkFavourite>
    <SelectionNo>3</SelectionNo>
    <SelectionDomain>106</SelectionDomain>
    <Report>6</Report>
    <SelectionType>1</SelectionType>
    <Period>0</Period>
    <Levels></Levels>
    <Interval></Interval>
    <SelectedCode></SelectedCode>
    <FlgFillEmpty>0</FlgFillEmpty>
  </Selection>
  <Selection>
    <PkFavourite>61057</PkFavourite>
    <SelectionNo>4</SelectionNo>
    <SelectionDomain>4902</SelectionDomain>
    <Report>6</Report>
    <SelectionType>1</SelectionType>
    <Period>0</Period>
    <Levels></Levels>
    <Interval></Interval>
    <SelectedCode></SelectedCode>
    <FlgFillEmpty>0</FlgFillEmpty>
  </Selection>
  <Selection>
    <PkFavourite>61057</PkFavourite>
    <SelectionNo>1</SelectionNo>
    <SelectionDomain>357</SelectionDomain>
    <Report>6</Report>
    <SelectionType>1</SelectionType>
    <Period>0</Period>
    <Levels></Levels>
    <Interval></Interval>
    <SelectedCode></SelectedCode>
    <FlgFillEmpty>0</FlgFillEmpty>
  </Selection>
  <Selection>
    <PkFavourite>61057</PkFavourite>
    <SelectionNo>2</SelectionNo>
    <SelectionDomain>107</SelectionDomain>
    <Report>6</Report>
    <SelectionType>3</SelectionType>
    <Period>5</Period>
    <Levels></Levels>
    <Interval></Interval>
    <SelectedCode></SelectedCode>
    <FlgFillEmpty>0</FlgFillEmpty>
  </Selection>
  <Aggregate>
    <PkFavourite>61057</PkFavourite>
    <DescendFavourite>61057</DescendFavourite>
    <DescendLevel>1</DescendLevel>
  </Aggregate>
</Favourite>]]></GetDefinitionResult>
      </GetDefinitionResponse>
   </soap:Body>
</soap:Envelope>
How do I read ie. all the DomainNode in the Sort element with Xpath (preferably) or Xquery?
The database will be a 10.2.0.4 at most.

Update: so far I have got this
declare
 p_xml clob := '
 <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <GetDefinitionResponse xmlns="http://synergi.com">
         <GetDefinitionResult><![CDATA[<?xml version="1.0" encoding="utf-8"?>
<Favourite>
....
....
...
';

w_namespaces varchar2(100) :=  'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns="http://synergi.com"';
w_xpath varchar2(100) := '/soap:Envelope/soap:Body/GetDefinitionResponse/GetDefinitionResult/text()';
w_xpath2 varchar2(100) := '/*:DomainNode';
w_svar clob;
w_xml xmltype;
 
 procedure pr(p_in varchar2)
 as
 begin
   dbms_output.put_line(p_in);
 end;
 
 begin
    w_xml := xmltype(p_xml);
  
    select extract(w_xml,w_xpath,w_namespaces).getclobval()
    into w_svar
    from dual;
    
    pr('svar:' || w_svar);   
    
    /*
    select extractvalue(xmltype(w_svar),w_xpath2)
    into w_svar
    from dual;
    
    pr('svar2:' || w_svar);  */
   
 end;
I get an ora-01706 error when I just do the extract (field longer that allowed 4K) - so now I extract the whole content of <GetDefinitionResult> - but then the CDATA thing is pestering me :-)
Is there a smart way of getting rid of this CDATA including the <! and [[ and ]] ? I mean you can make CDATA with xmlcdata function ... but nbow I would like to get of rid of it.

end update -------------------------

regards
Mette
This post has been answered by odie_63 on Mar 26 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 23 2012
Added on Mar 26 2012
#performance-availability, #xml-db
8 comments
2,287 views