How to get XML from a BLOB field in Oracle Database 11g?

1
votes

This is the scenario, in a Oracle Database 11g, theres a BLOB field which has XML data which I need to query. I don't know how the XML was inserted into the blob field, but this is what I'm getting with a query using the DBMS_LOB.substr() function:

enter image description here

1
xmloracleoracle11gblob
That doesn't look like XML. - Sentinel
@Sentinel Could be after some encoding with convert() function? - Mr. D MX

1 Answers

3
votes

Here is how you do it:

  • 1. First you have to find out structure of XML that was inserted into BLOB. For example: select XMLTYPE(blob_field, 1) as XML from my_table;

  • 2. Now if you want to use substr function to extract specific string you can do the following: SELECT substr(XMLTYPE (UTL_RAW.cast_to_varchar2 (blob_field)).EXTRACT('xml_tag/xml_tag/../text()'),200,200) as "XML DATA" FROM my_table where pk = 123123;

In this example xml_tag/xml_tag/../text() is your xml structure. For example is my structure is <Profile><Name>Your Customer Name</Name></Profile>. So I will have to write as Profile/Name/text()

Hope it helps.