Search code examples
sqlxmloracleblobxmltype

How to extract data (via SQL query) from XML stored in a column as type BLOB

I need help with the following. I have a table which has one of the columns as type BLOB. Executing following query allows me to read the BLOB in the Oracle SQL developor's text editor as the image below:

select  
utl_raw.cast_to_varchar2(utl_compress.lz_uncompress(a.DATA))
 from my_table a where a.id = 11266392;

enter image description here

I can double click on above shaded cell and read it in text. This is nice but it only works for one row at a time. My actual goal is to read specific data within xml tags from each of these BLOBs and there are roughly a million of those for each month.

I thought i could cast this into a string and read the first 4000 characters but that was useless as my BLOBs for each record are of length 400K (using getlength procedure from dbms_lob).

I tried casting the blob in XML via this

select CONVERT(xml,a.data, 2) from
(select utl_compress.lz_uncompress(a.DATA) as data  from my_table a where a.id = 11266392) a;

But this threw an error:

ORA-00904: "XML": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 10 Column: 16

My question is then, is there a way I can use extract XML, XQuery or XMLTable on the blob itself (after I have applied the lz_uncompress function on it)? What are my other alternatives?

Here is the heavily edited XML in one of the blob (note that I have about 10-15 such Worksheet tags)

<Worksheets>
  <Worksheet Description="Some Coverage" EffectiveDate="2020-06-28T00:01:00-05:00" ExpirationDate="2021-06-28T00:01:00-05:00" FixedId="Table:13263928">
    <Routine RateBookCode="XX" RateBookEdition="00006" RoutineCode="XXX" RoutineVersion="1">
      <Store Declaration="true" Result="false" ResultType="java.lang.Boolean" Variable="hasSpecialLimits">
        <PropertyGet ObjectName="XXX" ObjectType="XXX" ObjectValue="My Address" PropertyName="HasSpecialLimits" Type="XXXXX" Value="false" ValueType="java.lang.Boolean"/>
      </Store>
    </Routine>
  </Worksheet>
</Worksheets>
Solution

  • It should work like this:

    SELECT XMLTYPE( a.data, 2 )
  FROM ( select utl_compress.lz_uncompress(a.DATA) as data
           from my_table a where a.id = 11266392 ) a;