How to check for a null value in BLOB column?-CodePudding

I have a table ProfilePictures

PhotoNumber, photoinfo
int        , BLOB

Sample data in photoinfo column:

<DataIM>
  <PhotoChosen>
    <Photo>
      <jpg>my_photo.jpg</jpg>
      <jpg>other_photo.jpg</jpg>
    </Photo>
  </PhotoChosen>
</DataIM>

----------------

<DataIM>
  <PhotoChosen>
    <Photo>
      <jpg>my_photo.jpg</jpg>
      <jpg>other_photo.jpg</jpg>
    </Photo>
  </PhotoChosen>
 <caption>This is another photo</caption>
</DataIM>

The tag <caption><\caption> is not in every record.

I want to fetch the text in the XML. So, I wrote the below query which is working fine.

Select PhotoNumber,
    XMLQuery(
        '/DataIM/caption[1]/text()'
        passing xmltype(photoinfo, 0)
        returning content
    ).getstringval() "photoinfo"
From ProfilePictures 
Where photosourcetype = 10

After running this query, I get the result as

PHOTONUMBER | photoinfo   
----------- | ------------
         42 | null
         43 | null
         44 | This is another photo

I want to filter out records have don't have null in photoinfo column and also want to include a filter on the text of photoinfo column. For example, the text should contain "another".

When I used the is not null, it didn't work and it retrieved null values as well.

My modified query:

Select PhotoNumber,
    XMLQuery(
        '/DataIM/caption[1]/text()'
        passing xmltype(photoinfo, 0)
        returning content
    ).getstringval() "photoinfo"
From ProfilePictures 
Where photosourcetype = 10 and photoinfo is not null

This didn't work. So, how should I write this query that I can filter out the null values as well as check for "another" in text?

My expected answer should be

PHOTONUMBER | photoinfo   
----------- | ------------
         44 | This is another photo

CodePudding user response：

Wrap the query in an outer query and then check for the NULL value in the outer query:

SELECT *
FROM   (
  Select PhotoNumber,
         XMLQuery(
           '/DataIM/caption[1]/text()'
           passing xmltype(photoinfo, 0)
           returning content
         ).getstringval() AS photoinfo
  From ProfilePictures 
  Where photosourcetype = 10
)
WHERE  photoinfo IS NOT NULL;

or use XMLEXISTS:

SELECT PhotoNumber,
       XMLQuery(
         '/DataIM/caption[1]/text()'
         PASSING XMLTYPE(photoinfo, 0)
         RETURNING CONTENT
       ).getStringVal() "photoinfo"
FROM   ProfilePictures 
WHERE  photosourcetype = 10
AND    XMLEXISTS( '/DataIM/caption[1]' PASSING XMLTYPE(photoinfo, 0) );

or use XMLTABLE:

SELECT p.PhotoNumber,
       x.photoinfo
FROM   ProfilePictures p
       CROSS APPLY XMLTABLE(
         '/DataIM'
         PASSING XMLTYPE(p.photoinfo, 0)
         COLUMNS
           photoinfo VARCHAR2(400) PATH 'caption'
       ) x
WHERE  p.photosourcetype = 10
AND    x.photoinfo IS NOT NULL;

or, using XMLTABLE with the XPATH to the caption in which case the XMLTABLE will not generate a row and the CROSS APPLY will not have anything to match and the IS NOT NULL check is not needed:

SELECT p.PhotoNumber,
       x.photoinfo
FROM   ProfilePictures p
       CROSS APPLY XMLTABLE(
         '/DataIM/caption'
         PASSING XMLTYPE(p.photoinfo, 0)
         COLUMNS
           photoinfo VARCHAR2(400) PATH '.'
       ) x
WHERE  p.photosourcetype = 10;

db<>fiddle here

CodePudding user response：

Contents you store in that BLOB column isn't binary, so - why didn't you use a CLOB instead?

For sample table:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 PHOTO                                              BLOB

and its contents

SQL> select id, photo, dbms_lob.getlength(photo) len
  2  from test;

        ID PHOTO                                                     LEN
---------- -------------------------------------------------- ----------
      5034
      6982 89504E470D0A1A0A0000000D49484452000000590000006808      15468
           02000000154FCBF000003C334944415478DAAD7D0998255775
           5EEDF5F6BDBB5F2FB369F61969464212DA05C20884C0C6605B
           80C03126B1

you can use dbms_lob.getlength to skip rows whose BLOB datatype column is empty:

SQL> select id
  2  from test
  3  where dbms_lob.getlength(photo) is not null;

        ID
----------
      6982

SQL>