nvarchar concatenation problem.

Question

Hi All, 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

Answer 2

If you concatenate a string with null, the result is null. So, if only one of the values that you get is null, the entire result will end up as null.

You can use isnull( ... , '') to turn any value that might be null value into an empty string.

Also, instead of using a lot of selects from the same table, you can use a single select:

select @strXml =
  '<PromoData><Promotion>' +
  '<PromoName>' + PromoName + '</PromoName>' +
  '<PromoCode>' + PromoCode + '</PromoCode>' +
  ...
  '<AreaID>' + convert(nvarchar, AreaID) + '</AreaID>'
  '</Promotion></PromoData>'
from #Temp

Answer 3

If any of the values in a concatenation is NULL, than the resulting string is also NULL. If you just wanted the NULL value to be blank, than you can use one of the SQL NULL functions to return an empty string instead. See W3Schools SQL NULL Functions for a complete list of supported functions for different SQL engines.

Answer 4

Any expression that contains NULL evaluates to NULL. So a single NULL in any column will wipe out all other values. You can use the ISNULL function to get around this problem. Consider structuring your code this way:

DECLARE @xml nvarchar(max)

SET @xml = 
(
    SELECT ISNULL(PromoName,'') AS PromoName
        , ISNULL(PromoDesc,'') AS PromoDesc
        , ISNULL(PromoCode,'') AS PromoCode
        --etc.
    FROM #Temp
    FOR XML RAW('Promotion'), ELEMENTS
)

This example also uses a simple version of the SQLServer FOR XML clause. If you're translating data into XML strings in TSQL, I would recommend that you take a look at this language feature. This technique does not apply to other database platforms.

Answer 5

As others have pointed out, concatenating a string with NULL yields NULL. SQL Server has an option to control this, so putting SET CONCAT_NULL_YIELDS_NULL OFF before your query should correct your problem.