Return '0' INT while reading XML attribute in SQL

Question

How can I return INT value 0 instead of NULL from XML? Please consider the following:

DECLARE @xml XML ='<asset> <cnfg categ="Desktop" type="hj" device="3"/></asset>';        

DECLARE @Str1 INT;
DECLARE @Str2 INT;

SELECT  @Str1 = ParamValues.Content.value('@dev', 'int'),
        @Str2 = ParamValues.Content.value('@device', 'int') 
FROM @xml.nodes('/asset/cnfg') as ParamValues(Content)

SELECT @Str1, @str2

The above code returns NULL,3, but I want to get the result as 0,3. How can this be achieved?

Answer 1

I think you just need coalesce():

SELECT @Str1 = COALESCE(ParamValues.Content.value('@dev', 'int'), 0),
       @Str2 = COALESCE(ParamValues.Content.value('@device', 'int'), 0)
FROM @xml.nodes('/asset/cnfg') as ParamValues(Content)