Oracle query parses XML from blob with header

I have a table i开发者_如何学编程n 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.

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;