[PL/SQL] Extract data from XML BLOB

What if you need to query a stored XML, but it was unfortunately stored as a BLOB instead of a CLOB?

You can easily convert it to a CLOB first, then query it with:

 DECLARE  
   
 l_XML XMLTYPE;   
 l_blob BLOB;  
 l_clob CLOB;  
 l_dest_offsset PLS_INTEGER := 1;  
 l_src_offsset PLS_INTEGER := 1;  
 l_lang_context PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;  
 l_warning PLS_INTEGER;  
   
 l_myData MYTYPE;  
        
 BEGIN  
   
 --get the blob  
 SELECT myBlob  
 INTO l_blob  
 FROM myTable  
 WHERE something;  
   
 --create empty CLOB  
 DBMS_LOB.CREATETEMPORARY(l_clob, true);  
    
 DBMS_LOB.CONVERTTOCLOB(  
      dest_lob => l_clob,  
      src_blob => l_clob,  
      amount => DBMS_LOB.LOBMAXSIZE,  
      dest_offset => l_dest_offsset,  
      src_offset => l_src_offsset,  
      blob_csid => DBMS_LOB.DEFAULT_CSID,  
      lang_context => l_lang_context,  
      warning => l_warning  
 );  
   
 --crea XML from CLOB  
 l_XML := XMLTYPE.CREATEXML(l_clob);  
   
 --extract data from the XML  
 BEGIN  
 SELECT EXTRACTVALUE(  
   l_xml,  
   '//root/node/text()',  --be sure to match your XML tree naming! - /text() is optional, use it if the node content is CDATA
   'xmlns="OPTIONAL_XMLNS_HERE"' --optional, if you don't need it, do not even pass it to the function   
 ) node_value  
 INTO l_myData  
 FROM DUAL;  
 EXCEPTION WHEN NO_DATA_FOUND THEN --optional exception block  
      --something  
 WHEN OTHERS THEN  
      --something else  
 END;  
   
 END;