Oracle query parses XML from blob with header

Question

I have a table in an oracle 10 database, containing a field with a BLOB (not CLOB). This BLOB contains a fixed-size header of about 300 bytes, followed by an XML document. The blob might have a size up to about 1 megabyte. I'd like to create an SQL query which uses XQUERY on this document to extract information from the XML.

So the structure is, roughly:

create table tbl(id integer, data blob);

insert into tbl(id,data) value(1,'HEADER <?xml version="1.0"><data>
  <row key="k1" value="v11"/>
  <row key="k2" value="v12"/></data>');

insert into tbl(id,data) value(2,'HEADER <?xml version="1.0"><data>
  <row key="k1" value="v21"/>
  <row key="k1" value="v21B"/>
  <row key="k2" value="v22"/></data>');

I'd like a query on this table which, when given key k1, returns values v11,v21 and v21B

I know this data organisation is suboptimal, but it can't be changed.

Jim Hudson · Accepted Answer · 2011-07-18 13:57:28Z

1

OK, so first you have to get the XML part. Assuming that the header and XML are both character data, and the header is a fixed length, I'd probably use a combination of

dbms_lob.converttoclob to turn the blob into a clob dbms_lob.substr to get a clob that has the XML portion xmltype.createXML (clob) to assign the XML to your xmltype xmltype.extract to apply your xpath expression

If the header isn't character data, you can still use dbms_lob.substr but it will return a RAW which you'll need to convert. If the header isn't fixed length, you can search for the location of the

So, based on the comments, use something like this to build a clob that has what you want, where offset is the number of bytes to the start of your actual XML. Modify to pass in your blob or clob. Then apply your xpath at the end instead of my dbms_output.

declare
   v_buffer varchar2(32767);
   v_offset integer := 5;
   v_xml xmltype;
   v_clob clob;
   v_input clob := 'xxxx<?xml version="1.0" encoding="UTF-8"?><test>This is a test</test>';
   i integer := 0;
begin
   dbms_lob.createtemporary (v_clob,true);
   v_buffer := dbms_lob.substr(v_input,32767,v_offset);
   while length (v_buffer) > 0 loop
      v_clob := v_clob || v_buffer;
      i := i + 1;
      v_buffer := dbms_lob.substr(v_input,32767, v_offset + i * 32767);
   end loop;
   dbms_output.put_line ('x'||v_clob||'x');
   v_xml := xmltype.createXML (v_clob); 
   dbms_lob.freetemporary (v_clob);
   dbms_output.put_line (v_xml.getclobval);
end;

edited Jul 18, 2011 at 13:57

answered Jul 14, 2011 at 13:07

Jim Hudson

8,1092 gold badges26 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hyperman Over a year ago

I tried. As far as i understand it, dbms_lob.substr truncates the XML at 32767 bytes, but they can be up to 1MB

Jim Hudson Over a year ago

You're right, the spec is DBMS_LOB.SUBSTR ( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET; so you need to loop. First call to get the first 32K bytes, then call with an offset of 32768 and append the buffer to your clob, and so forth.

hyperman Over a year ago

That's the point where I am stuck: How do i aggregate all these 32K parts in a SELECT? Or if i put it in a BEGIN...END block, can i return data from this block as if it were a SELECT? Also, as i have only read rights on the schema, how do i concatenate all these 32K parts in-memory without hitting the 32K limitation again?

hyperman Over a year ago

Thanks, il try this. It seems to cover all my problems

Collectives™ on Stack Overflow

Oracle query parses XML from blob with header

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related