How to check for a null value in BLOB column?

Question

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

Answer 1

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