convert oracle blob to xml type

Question

I have experience using MSSQL 2008 and I recently had to move from MSSQL to Oracle 10g. The people who designed the (Oracle) table, which has a column I need to extract data from, used a BLOB type column for the XML they need to store.

In MSSQL you would have simply stored your XML string in an XML type or used a VARCHAR(MAX). Assume a table myTable with a column called myColumn which is a VARCHAR(MAX) containing <ROOT><a>111</a></ROOT> If you wanted to convert the VARCHAR(MAX) type to an XML type you would simply write something like:

SELECT CONVERT(XML, myColumn) FROM myTable

if you wanted, you could then use XQuery to get data from the converted column, like so:

SELECT CONVERT(XML, myColumn).query('/ROOT/a')

How would you accomplish the same thing in Oracle 10g if myColumn was a BLOB, without having to write a stored procedure but still making it reusable? The text in the BLOB is UFT-8.

I would really appreciate your assistance, as I kind of need this in a hurry.

Solution 1

select
XMLType( BLOB_COLUMN,
         1 /* this is your character set ID.
                   1 == USASCII */
       ) as XML
from my_table;

Solution 2

You can convert from a BLOB to a CLOB and then pass the CLOB into the constructor of XMLTYPE. Here's a function...

-- PL/SQL function to convert a BLOB to an XMLTYPE
-- Usage: SELECT blob_to_xmltype(blob_column) FROM table_name;

CREATE OR REPLACE FUNCTION blob_to_xmltype (blob_in IN BLOB)
RETURN XMLTYPE
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 XMLTYPE(v_clob);
END blob_to_xmltype;
/

And for your specific example above you can use the EXTRACT() function:

SELECT extract(blob_to_xmltype(myColumn), '/ROOT/a') FROM table_name;

The above will return another XMLTYPE. If you want to get the text value of the node, you can use the EXTRACTVALUE() function instead.

Solution 3

if myColumn was a BLOB

SELECT EXTRACT(XMLTYPE.CREATEXML(myColumn), '/ROOT/a')
FROM myTable;

sources :

• http://lstierneyltd.com/blog/development/tips/how-to-convert-a-clob-to-an-xmltype-in-oracle/
• http://docs.oracle.com/cd/B10501_01/appdev.920/a96620/xdb04cre.htm#1024805

Comments

[user1279734]

Hi Davmos thank you very much for your response, I am trying out that solution that you gave. Will let you know how it works.

[user1279734]

Hi Davmos, I keep getting a clob back instead of and XMLTYPE, how do I get an xml type?

[davmos]

Hi @user1279734, the function above can only return XMLTYPE. How are you calling it?