SQL Server - contains an invalid XML identifier as required by FOR XML;

Question

I'm running this query and getting below mentioned error. Can anyone help?

Column name 'Middle Name' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.

SELECT 
    Username as [LastName],
    '' AS [Middle Name],
    '' AS Birthdate,
    '' AS [SSN],
    0 AS [Wage Amount]
    FROM
    Employee
     FOR XML PATH

Answer 1

You can't have spaces in XML element or attribute names. Use

SELECT Username AS [LastName],
       ''       AS [MiddleName],
       ''       AS Birthdate,
       ''       AS [SSN],
       0        AS [WageAmount]
FROM   Employee
FOR XML PATH