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

Question

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;

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>

Thomas Kirchhoff · Accepted Answer · 2021-02-02 16:48:56Z

1

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;

answered Feb 2, 2021 at 16:48

Thomas Kirchhoff

1,1181 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user27396 Over a year ago

This works. At least the data type of the result is XMLTYPE which I believe can be parsed via some standard methods. Can you advise me as to how to parse the XMLTYPE into meaningful columns? If you can point me to some resources online that can get me started that would be great. Thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related