How can I query xml inside a blob column?

I am trying to search xml that is contained within a blob column.
My first attempt was this:
select NAME,
extractValue(GDB_USERMETADATA.xml, '/METADATA/CITATION/CITEINFO/TITLE')
TITLE
from  GDB_USERMETADATA;
And I got this error:  ORA-00932: inconsistent datatypes: expected - got -

The following adviced from a users forum I tried this;

select NAME,
extractValue(xmltype(GDB_USERMETADATA.xml), '/METADATA/CITATION/CITEINFO/TITLE')
TITLE
from  GDB_USERMETADATA;
and I got this other error:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'XMLTYPE'

Thanks in advance
OR1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
you can convert a varchar2 or a clob to xmltype with a constructor

xmltype(varchar2_column)
xmltype(clob_column)

if you have a blob, you will need to extract the contents into a varchar2 or clob type
(probably clob if it's big)

use dbms_lob.converttoclob   to change your blob into a clob
0
 
slightwv (䄆 Netminder) Commented:
I might be wrong here but in case I'm not I wanted to add that dbms_lob.convertoclob isn't a SQL function.  This means you can't just add it to your select.

You will probably need to write your own function to wrap the XML extract call.
0
 
OR1Author Commented:
The column is a blob. Does than mean I have to export the data to another table?  I was trying to do make this part of a report I could generate quickly.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
slightwv (䄆 Netminder) Commented:
Not really export it to another table but you will likely need to write your own function to convert it to a CLOB first.

sdstuber can confirm this.  I don't want to take over his question.
0
 
sdstuberCommented:
yes, if the column is a blob, you need to convert it to a clob.

if the blob is bigger than 32767 bytes then you will need to call the dbms_lob.converttoclob function multiple times to copy the entire thing.

and, as slightwv suggested, it might be easiest to do that in your own function.

then you write your report like

extractValue(
       xmltype(your_function(yourblob_column)),
       '/METADATA/CITATION/CITEINFO/TITLE'
)

0
 
OR1Author Commented:
Following  some of your suggestions and using afunction I found in another forum I am able to query the information for one record at a time. when I try many records I get the following errors:

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 272
ORA-06512: at line 1

If i do this it works:
SELECT NAME,
  EXTRACTVALUE
     (XMLtype( BLOB_TO_CLOB(GDB_USERMETADATA.XML)), '/metadata//idinfo/citation/citeinfo/title') TITLE
  FROM GDB_USERMETADATA
 WHERE NAME = 'tl_2009_us_metdiv';

NAME,TITLE
tl_2009_us_metdiv,TIGER/Line Shapefile, 2009,  nation, U.S., Current Metropolitan Division National


This one fails
SELECT NAME,
  EXTRACTVALUE
     (XMLtype( BLOB_TO_CLOB(GDB_USERMETADATA.XML)), '/metadata//idinfo/citation/citeinfo/title') TITLE
  FROM GDB_USERMETADATA;



The function I am using is attached;


CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
   RETURN CLOB
AS
   v_clob      CLOB;
   v_varchar   VARCHAR2 (32767);
   v_start     PLS_INTEGER := 1;
   v_buffer    PLS_INTEGER := 32767;
BEGIN
   DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);

   FOR i IN 1 .. CEIL (DBMS_LOB.GETLENGTH (blob_in) / v_buffer)
   LOOP
      v_varchar :=
         UTL_RAW.
         CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (blob_in, v_buffer, v_start));
      DBMS_LOB.WRITEAPPEND (v_clob, LENGTH (v_varchar), v_varchar);
      v_start := v_start + v_buffer;
   END LOOP;

   RETURN v_clob;
END blob_to_clob;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Per Metalink, one of the XML files has an external reference like A DTD.

Check out Doc ID 803686.1
0
 
OR1Author Commented:
I saw it but I am trying to retrieve text elements not format the xml.  Furthermore the pproblem seems to happen when I query more than one record. I don't see why I need  the DTD. I will probably have to ask our DBA to contact tech support.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
The XML stored in the BLOB has the reference to a Schema/DTD/XSLT/???

When Oracle trys to parse it, it is unable to locate the external reference.

For example:
http://www.xmlfiles.com/dtd/dtd_intro.asp

External DTD:

<?xml version="1.0"?>
<!DOCTYPE note SYSTEM "note.dtd">
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
0
 
OR1Author Commented:
Thanks to both of you I was able to complete my script.  The error I was getting in the end was fixed by correcting two metadata files that had problems specifically duplicate entries.  

Thanks!
0
All Courses

From novice to tech pro — start learning today.