How can I query xml inside a blob column?

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.

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.

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.

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'
)

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;
/

Select allOpen in new window

Per Metalink, one of the XML files has an external reference like A DTD.

Check out Doc ID 803686.1

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.

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!