CODEWITHSUNDEEP
xmloracle11g
R3D3vil
R3D3vil

Reputation: 681

Error while extracting xml from a clob column

I have a Clob column in a table which contains xml data. I try to extract the data by writing a query:

select XMLTYPE.createxml(e.cdxml_index).extract('//page/fragment/text()') from chemical_structures e where e.primary_key=20;

The error message i get is:

Error report: SQL Error: ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side ORA-06512: at "SYS.XMLTYPE", line 5 31020. 00000 - "The operation is not allowed, Reason: %s" *Cause: The operation attempted is not allowed *Action: See reason and change to a valid operation.

Data in the clob column is as follows:

(CLOB) <?xml version="1.0"  ?>
<!DOCTYPE CDXML SYSTEM "http://www.***.com/xml/cdxml.dtd" >
<CDXML
 <page
 id="12"
 BoundingBox="0 0 540 719.75"
><fragment
 id="9"
 BoundingBox="91.5 111.75 104.01 123.21"
><n
id="8"
p="94.94 117.6"
Z="2"
Element="35"
NumHydrogens="0"
Charge="-1"
AS="N"
><t
id="7"
p="91.5 121.5"
BoundingBox="91.5 111.75 104.01 123.21"
><s font="3" size="10" face="96">Br-</s></t></n></fragment></page></CDXML>

I read on a forum that is has something to do with the DOCTYPE declaration in the xml.

can anyone suggest a way by which i can make it work?

Thanks

Upvotes: 1

Views: 6245

Answers (1)

R3D3vil
R3D3vil

Reputation: 681

I found a workaround.

I had to disable xml dtd validation but that didnt work after trying out a few things mentioned on various discussion boards.

Finally i decided to ignore the doctype declaration in the xml. To do that i used a REGEXP_REPLACE method.

The following query gave me what i was looking for:

select extract(XMLTYPE(REGEXP_REPLACE(e.cdxml_index, '<!DOCTYPE CDXML SYSTEM "http://***/xml/cdxml.dtd" >', '')),'//page/fragment/n') from chemical_structures e where e.primary_key=20;

I get the following output:

<n
id="8"
p="94.94 117.6"
Z="2"
Element="35"
NumHydrogens="0"
Charge="-1"
AS="N"
><t
id="7"
p="91.5 121.5"
BoundingBox="91.5 111.75 104.01 123.21"
><s font="3" size="10" face="96">Br-</s></t></n>

Upvotes: 3

Related Questions