SSRS automatically aliasing a subquery column using FOR XML

Question

I have a query running against a SQL Server database. One of the columns in it uses the STUFF() function.

When I run the query in a SQL editor window, the resulting value looks like this:

1234

When I try to use this query in an SSRS report, SSRS automatically puts a column alias within the STUFF() function, resulting in a value that looks like this:

<Expr1>, 1</Expr1><Expr1>, 2</Expr1><Expr1>, 3</Expr1><Expr1>, 4</Expr1>

The only difference between the two is that when I save the query in SSRS, the term "AS Expr1" is added automatically (see code below).

How can I prevent SSRS from adding this alias automatically and run the query as it is?

   SELECT 
        FirstName, 
        LastName, 
        CourseTitle,
        LastLoginDate,
        NoOfModules,
        COUNT(CourseCompleted) AS ModulesStarted,
        STUFF(
          (
            SELECT ','  + CAST(CourseModule AS varchar(20)) -- SSRS puts "AS Expr1" here
            FROM EDSF 
            WHERE 
                FirstName = e.FirstName AND 
                LastName = e.LastName AND 
                Coursecompleted = '1' AND 
                CourseTitle = e.CourseTitle
            FOR XML PATH('')
          ),1,1,''
         ) AS CoursesCompleted
    FROM EDSF  e
    WHERE 
        Coursecompleted = '1' OR 
        Coursecompleted = '0'
    GROUP BY 
        FirstName, 
        LastName, 
        CourseTitle,
        LastLoginDate,
        NoOfModules;

EDIT: the code has been rearranged and the question reworded to make the issue clearer. I'm NOT having a problem aliasing the returned column itself: instead, SSRS is actually adding an alias to the subquery within my STUFF() function, resulting in extra junk within the field itself.

Answer 1

I think there's some confusion in the comments originally: the question isn't about giving a column an alias, the question is why SSRS won't allow the subquery within the STUFF() command to exist without an alias. XML is interpreting that and including the column name in the field values itself, converting the input of "1", "2", "3", and "4" to (formatting as code because it's being interpreted as HTML):

"<Expr1>, 1</Expr1><Expr1>, 2</Expr1><Expr1>, 3</Expr1><Expr1>, 4</Expr1>" 

I'm pretty sure there's a way to tell FOR XML to ignore the column names, and I'm also pretty sure that the stored procedure option is a better way to go. That said, though, there are times a stored procedure isn't an option, and so I'll include a way of getting rid of the offending values with REPLACE()

SELECT 
    FirstName,
    LastName,
    CourseTitle,
    lastlogindate,
    Noofmodules, 
    COUNT(Coursecompleted) AS modulesstarted,
    REPLACE(REPLACE(REPLACE('<' + 
      STUFF((
        SELECT ','  + CAST(CourseModule AS varchar(20)) AS Expr1
        FROM Esdf 
        WHERE 
            FirstName = e.FirstName
            AND LastName = e.LastName
            AND Coursecompleted = '1'
            AND CourseTitle = e.CourseTitle
        FOR XML PATH('')),1,1,''
      ), '<Expr1>', ''), '</Expr1>', '') , ',', ''
     ) AS CoursesCompleted
FROM Esdf  e
WHERE 
    Coursecompleted = '1' OR
    Coursecompleted = '0'
GROUP BY 
    FirstName, 
    LastName, 
    CourseTitle,
    lastlogindate, 
    Noofmodules