Warning about implicit convert
While I agree with @Kin about data types, I don't think this warning is as troublesome as you think. You're performing grouped concatenation, which is going to be orders of magnitude more expensive than any conversions anyway (and as Daniel said, unless your catalog views are massive - as in larger than physical memory - it is unlikely to affect anything in terms of estimation either).

I would write the query this way, taking care to never use varchar strings (which also means stop leaving out the N prefix on string literals), and also ensuring you use statement terminators:

DECLARE @NameColumns nvarchar(max); -- why 1024 when you use max below?

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

SELECT @NameColumns;


Even still, I don't believe there is any way to avoid the implicit convert without changing the output by avoiding TYPE/value() as Mikael suggested. I would say worry about this when you can actually prove that this has some material impact on the performance of the query. In my tests, the two different forms performed the same (we're talking sub-10ms every time), but of course if I have a table named Sales &amp; Stuff then it becomes Sales &amp;amp; Stuff without the implicit convert.