Trying to pivot based on a dynamically created query which generates XML data

You don't need FOR XML here. This is sufficient:

DECLARE @ColumnList NVARCHAR(MAX) = '';

WITH Data AS (
        SELECT DISTINCT name
        FROM   sys.tables
        )
        SELECT  @ColumnList +=', ' + name
        FROM    Data;

SET @ColumnList = STUFF(@ColumnList, 1, 2, '');