Oracle XML DB - extract function fails to parse the XML but after commiting the records, everything works fine.

Hi All,

Below is one of the queries we run where we face the problem.

SELECT replace(replace(XMLTYPE.Extract(XMLType(xml_file),'/ContentDetails/ContentComponent/Component[@name="Summary"]/Value/text()').getStringVal(),'<![CDATA[',''),']]>','')
FROM <tableName>
WHERE <condition>

When we run above query usign java class, the error we get in our logs is as follows:

2005-12-30 14:16:53,104 [Thread-17] ERROR com.ge.cms.content.ContentUtils - java.sql.SQLException: ORA-31011: XML parsing failed

ORA-19202: Error occurred in XML processing
LPX-00200: could not convert from encoding UTF-8 to UCS2
Error at line 237
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 1

java.sql.SQLException: ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00200: could not convert from encoding UTF-8 to UCS2
Error at line 237
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 1

The query uses the extract function and retrieve the value of summary node from XML stored in XML_FILE column of table. The XML content in the column are UTF-8 encoded and works fine when they are displayed using Java parsing methods.  Below is the complete XML pasted for your reference.

Strangely, when we touch (deleting the summary, saving it, committing it, changing the summary back to original, saving it and committing it again) this XML in TOAD, it works just fine. Next time onwords it does not create any problem unless we create a new record from the frontend.  

Please suggest. Will appreciate and be greateful for any of the conmments on above problem.

----------------------------
<ContentDetails>
<ContentType Type="STORY">
</ContentType>

 <ContentInfo>
</ContentInfo>

     <ContentComponent type="Header">

<Component name="ContentID">
<Value><![CDATA[81925]]></Value>     </Component>

<Component name="headline">
      <Value><![CDATA[Most Admired]]></Value>
     </Component>

<Component name="Date">
      <Value><![CDATA[01/03/2006]]></Value>
     </Component>

<Component name="Source">
      <Value><![CDATA[11642]]></Value>
     </Component>

<Component name="Category">
      <Value><![CDATA[11709]]></Value>
     </Component>

<Component name="Section-Page">
      <Value><![CDATA[sdfsdfs]]></Value>
     </Component>

<Component name="Author">
      <Value><![CDATA[dfsdfsdfsd]]></Value>
     </Component>

<Component name="URL">
      <Value><![CDATA[]]></Value>
     </Component>

<Component name="HeadlineRefType">
     </Component>




     </ContentComponent>
     <ContentComponent type="Story">


<Component name="Summary">
     <Value><![CDATA[ was named one of the ten most admired companies in Brazil at a ceremony hosted by Mr. Luiz Inácio Lula da Silva, President of Brazil. ááá]]></Value>
     </Component>

<Component name="Body">
     <Value><![CDATA[ement was made this week in a ceremony held in São Paulo. Alexandre Silva, president for ceived the Award from Mr. Luiz Inácio Lula da Silva, President of Brazil, and Mr. Mino Carta, Director of Carta Capital magazine. <BR>&nbsp;<BR>This was the 8th edition of the award which&nbsp; takes place every year to recognize companies with outstanding performance towards ethics, social responsibility and solid financial capabilities.&nbsp; <BR>&nbsp;<BR>GE was one of only three multinational companies amongnst&nbsp; the winners&nbsp; jointly with Microsoft and Nestlé. Other companies recognized with the Award are Natura, Vale do Rio Doce, Gerdau, Tam, Votorantim, Embraer, Petrobrás and Itaú.&nbsp;&nbsp; <BR>&nbsp;<BR>"We are very proud with this award. It is a valuable recognition for all the efforts  has put together while developing its activities in Brazil", said Alexandre Silva .<BR>&nbsp;<BR>Besides president Luiz Inácio Lula da Silva, other important guests were present, including Ministers, opinion leaders , top executives and businessmen.<BR>]]></Value>
     </Component>



     </ContentComponent>
     <ContentComponent type="Poll">



<Component name="Question">
      <Value><![CDATA[]]></Value>
     </Component>

<Component name="Button">
     </Component>

<Component name="Answer">
      <Value><![CDATA[]]></Value>
     </Component>

<Component name="Answer">
      <Value><![CDATA[]]></Value>
     </Component>

<Component name="Answer">
      <Value><![CDATA[]]></Value>
     </Component>

<Component name="Answer">
      <Value><![CDATA[]]></Value>
     </Component>

<Component name="Answer">
      <Value><![CDATA[]]></Value>
     </Component>


     </ContentComponent>
     <ContentComponent type="RelatedLinks">




<Component name="Link">
<URL>http://</URL>
<Title><![CDATA[]]></Title>
     </Component>

<Component name="Link">
<URL>http://</URL>
<Title><![CDATA[]]></Title>
     </Component>

<Component name="Link">
<URL>http://</URL>
<Title><![CDATA[]]></Title>
     </Component>

<Component name="Link">
<URL>http://</URL>
<Title><![CDATA[]]></Title>
     </Component>

<Component name="Link">
<URL>http://</URL>
<Title><![CDATA[]]></Title>
     </Component>

<Component name="Link">
<URL>http://</URL>
<Title><![CDATA[]]></Title>
     </Component>

     </ContentComponent>
<ContentComponent type="Image">
</ContentComponent>
<ContentComponent type="Reference">
</ContentComponent>

</ContentDetails>
--------------------------------

thanks,
jnajayjain.


Start Free Trial