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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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) 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.