We help IT Professionals succeed at work.

XMLTYPE and how to handle NULL

ehart12
ehart12 asked
on
I am using the following SQL line that pulls and parses XML data from an Oracle database.

xmltype(ssn.serial_lot_number).extract('//Serial/text()').getStringVal() as xml_serial,
xmltype(ssn.serial_lot_number).extract('//Lot/text()').getStringVal() as xml_lot,

I am finding that several database records have NULL in the serial_lot_number field which caused an error with the SQL statement. How do I account for NULL?

Thank you,

Eric
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Try this:

xmltype(nvl(ssn.serial_lot_number,'<a/>')).extract('//Serial/text()').getStringVal() as xml_serial
Most Valuable Expert 2011
Top Expert 2012
Commented:
CASE
           WHEN serial_lot_number IS NULL THEN NULL
           ELSE xmltype(ssn.serial_lot_number).EXTRACT('//Serial/text()').getstringval()
       END
           AS xml_serial,
       CASE
           WHEN serial_lot_number IS NULL THEN NULL
           ELSE xmltype(ssn.serial_lot_number).EXTRACT('//Lot/text()').getstringval()
       END
           AS xml_lot
Most Valuable Expert 2011
Top Expert 2012
Commented:
slightwv and I have both made the assumption you want NULL values returned for NULL xml content.

another option is to simply eliminate those rows from your result set


where serial_lot_number is not null

Author

Commented:
Thank you both for the comment! Your assumptions were correct as I did want to display the NULL xml object since there are other objects for each record that i also wanted to display.

Thank you guys,

Eric,