How to check if an XML type field is null or empty in SQL SERVER

Question

I need to check the amount of 0, NULL or empty in fields of different types, My difficulty is when it comes to fields of type XML. I tried to include in my logic the function exist(), but I do not know if it is the best way to do this check, using the code I have done before.

/*vérifie si la colonne numérique a des zéros et si la colonne avec des textes est vide (ou ne contient que espaces)*/

if @dataType IN ('int', 'bit', 'smallint', 'numeric', 'float', 'bigint', 'varbinary')
    set @checks = '[' +@colName +']' + '=0'

else if @dataType IN ('varchar', 'char', 'nvarchar', 'date', 'datetime')
    set @checks = ' ltrim(rtrim(['+ @colName + '])) ' + 'like '''''

else if @dataType IN ('xml')




/*vérifie si est nullable et si la colonne est nule*/
if @isNullable = 1
if LEN(@checks)> 0
    set @checks = @checks + ' OR [' + @colName + '] IS NULL '
else
    set @checks = '[' +@colName + '] IS NULL '


set @queryStat = @queryStat + @checks

EXEC sp_executesql @queryStat, @paramDefinition , @cnt = @vides output

Answer 1

You don’t have to make a if for the xml type; just check if the field is null. For this you must have @isNullable = 1 and will enter this section that makes the check:

/*vérifie si est nullable et si la colonne est nule*/
if @isNullable = 1
if LEN(@checks)> 0
    set @checks = @checks + ' OR [' + @colName + '] IS NULL '
else
    set @checks = '[' +@colName + '] IS NULL '