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.

Answer 1

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