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:
2
-
4That doesn't look like XML.Sentinel– Sentinel2016-12-15 18:51:10 +00:00Commented Dec 15, 2016 at 18:51
-
@Sentinel Could be after some encoding with convert() function?Lester– Lester2016-12-15 22:13:38 +00:00Commented Dec 15, 2016 at 22:13
Add a comment
|
1 Answer
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.
1 Comment
Lester
Thanks, it seems that the field does not have xml, maybe some binary data. I ended using a web service to get the info.