nvarchar concatenation problem

Question

I am probably trying to do a simple thing after dealing with all the hard stuff but seems like this is giving me a headache. I am trying to concatenate text into variable @strXml which is an nvarchar variable to build Xml element, but @strXml returns null. Please help. I am posting my code below.

DECLARE @strXml nvarchar(max) = ''
SET @strXml = '<PromoName>' + (Select PromoName From #Temp) + '</PromoName>' 
SET @strXml = @strXml + '<PromoDesc>' + (Select PromoDesc From #Temp) + '</PromoDesc>'
SET @strXml = @strXml + '<PromoCode>' + (Select PromoCode From #Temp) + '</PromoCode>' 
SET @strXml = @strXml + '<BeginDate>' + (Select Convert(nvarchar, BeginDate) From #Temp) + '</BeginDate>'
SET @strXml = @strXml + '<EndDate>' + (Select Convert(nvarchar, EndDate) From #Temp) + '</EndDate>'
SET @strXml = @strXml + '<RawHtml>' + (Select RawHtml From #Temp) + '</RawHtml>'
SET @strXml = @strXml + '<FocusPromoInd>' + Convert(nvarchar, 0) + '</FocusPromoInd>'
SET @strXml = @strXml + '<ParentPromoCode>' + (Select ParentPromoCode From #Temp) + '</ParentPromoCode>'
SET @strXml = @strXml + '<ActiveInd>' + (Select Case When ActiveInd=1 Then '1' Else '0' End From #Temp) + '</ActiveInd>'
SET @strXml = @strXml + '<AreaID>' + (Select Convert(nvarchar, AreaID) From #Temp) + '</AreaID>'
SET @strXml = '<PromoData><Promotion>' + @strXml + '</Promotion></PromoData>'
Select @strXml as strXML

And when I run the last query it returns null. Even in debug mode I cannot see @strXml updated with value on each line. Please help! Thanks.

Answer 1

One of the fields that you are concatenating is null.

Anytime you concatenate a string and a null value in SQL, the result is null.

You can use the COALESCE command to fix that:

declare @strXml nvarchar(max) = ''
set @strXml = '<PromoName>' + (select coalesce(PromoName, '') from #Temp) +
    '</PromoName>'
-- and so on