Warning about implicit convert

I have table with 2 name columns:

CREATE TABLE Test
(
  TestID int identity primary key clustered
  , Name_Eng nvarchar(50)
  , Name_Nat nvarchar(50)
)

Now I need a query to get this Name column separated by ,, like this:

DECLARE @NameColumns NVARCHAR(1024)

            SET @NameColumns = STUFF(
      (SELECT   ',' + 'Test.' + name AS [text()]
       FROM     ( SELECT    c.name
                  FROM      sys.columns c 
                            INNER JOIN sys.tables t ON t.object_id = c.object_id
                  WHERE     t.name = 'Test'
                            AND c.name LIKE 'Name_%'
                ) AS D
                FOR                  XML PATH('') ,
                                         TYPE).value('.[1]', 'VARCHAR(MAX)'), 1, 1,
                                     N'')

select  @NameColumns

But this query has a warning in the execution plan:



Is there any way to remove this warning?

The warning is there because of the XML function value(). The second parameter to value() is what you want the value stored in the XML to be converted to. You could argue that this is not in fact an implicit conversion but a very explicit conversion since you are asking for it to happen. Perhaps something for a connect item to suggest to Microsoft.

Simplest way to reproduce what you see.

declare @X xml;
select @X.value('text()[1]', 'int');


Gives these two warnings.







As you can see you get it with int's as well and you get two of them for each invocation of value().

declare @X xml;
select @X.value('text()[1]', 'int'),
@X.value('text()[1]', 'bit');










The conversion is done in the Stream Aggregate operator that calculate the value like this.

MIN(CASE WHEN [@X] IS NULL
THEN NULL
ELSE
CASE WHEN datalength(XML Reader with XPath filter.[value])>=(128)
THEN CONVERT_IMPLICIT(int,XML Reader with XPath filter.[lvalue],0)
ELSE CONVERT_IMPLICIT(int,XML Reader with XPath filter.[value],0)
END
END)


The result from the Table Valued function is returned in the lvalue or value column. The expression checks by using datalength to figure out where it should be fetched from and then converts it to your desired datatype.


Is there any way to remove this warning?

Yes there is. You remove the TYPE directive from the FOR XML PATH statement and you remove the call to the value() function. A side effect of doing that is that values you concatenate that contains characters that needs to be encoded in the XML like &<> will be encoded in your result.

StackExchange Database Administrators Q#126632, answer score: 10