How to convert XML type return text into select columns

I’m trying to get the column names of a table using XML datatype and information_schema columns. When I tried to use the result in another select statement, I have the results with the repeated column name instead of the results set. I have even tried to cast it to varchar but it still failed. what have done wrong ?

DECLARE @TSQL1 varchar(1000);

SELECT @TSQL1 = CAST((SELECT SUBSTRING((SELECT ', ' + QUOTENAME(COLUMN_NAME) 
                      FROM [ProdLS].[ information_schema.columns] 
                      WHERE table_name = 'roles' 
                      ORDER BY ORDINAL_POSITION 
                      FOR XML PATH('')), 3, 200000)) AS varchar(max));

 SELECT @TSQL1 
 FROM [aubeakon_scrm4].[acl_roles]

My query to get the results from roles table using the column name retrieved from.

Answer

You cannot execute dynamic SQL like that. You need to use sp_executesql. You also need to declare dynamic SQL as nvarchar(max).

You should also use .value to unescape the XML

DECLARE @TSQL1 nvarchar(max) = N'
SELECT 
' + STUFF((
    SELECT ', ' + QUOTENAME(COLUMN_NAME) 
    FROM [ProdLS].[information_schema].columns
    WHERE table_name = 'roles' 
    ORDER BY ORDINAL_POSITION 
    FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)'), 1, LEN(', '), '') + '
FROM [aubeakon_scrm4].[acl_roles];
';

EXEC sp_executesql @TSQL1;