FOR XML could not serialize the data for node because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

This error is very common for those who are using xml data. In my case we are using XML for  mails having formatted data instead attachment in monitoring mails. And one of old job suddenly starts failing with this error.

The error can come up with different messages also like XML could not serialize the data for node  because it contains a character (0x0000) , (0x0001), (0x0019)…

Quick fix is convert data to varbinary, Explanation of how to do is below 

FIX : ELSE CAST(st.text AS VARBINARY) END),

My Code looks like below and highlighted code is a issue

/**************************************************************/

SET @tableHTML = 

   N'<H1>Blocking on  ' + 

   N' Servername : ' + @Servername  + '</H1>' + 

   N'<table border="1">' + 

   N'<tr><th>spid</th><th>Blocked</th><th>HostName</th>' + 

   N'<th>Program Name</th><th>User</th><th>DB_Name</th><th>Current Query</th><th>Waittime</th>' + 

   CAST ( ( SELECT td = CAST(spid AS VARCHAR(10)),       '', 

       td = CAST(blocked AS VARCHAR(10)), '', 

       td = CAST(hostname AS VARCHAR(100)), '', 

       td = CAST(program_name AS VARCHAR(100)), '', 

       td = ISNULL(CAST(nt_username AS VARCHAR(30)),' '), '', 

       td = ISNULL(CAST(DB_NAME(dm.dbid) AS VARCHAR(30)),' '), '', 

       td = (CASE WHEN (st.text IS NULL) THEN Isnull(st.text,'--') ELSE CAST(st.text AS VARCHAR(500)) END), '',

       td = Waittime/60000 

       FROM master..sysprocesses dm 

       --LEFT JOIN SYS.DM_EXEC_REQUESTS dm on dm.session_id=spid 

       OUTER APPLY SYS.DM_EXEC_SQL_TEXT(dm.sql_handle) AS st 

       WHERE (blocked != 0 and spid != blocked and cmd != 'DB MIRROR') 

       OR (spid IN (SELECT s.blocked FROM master..sysprocesses s where s.blocked != s.spid) AND blocked = 0 AND cmd != 'DB MIRROR') 

       ORDER BY blocked 

       FOR XML PATH('tr'), TYPE  

   ) AS NVARCHAR(MAX) ) + 

   N'</table>' ;

/**************************************************************/

So the feasible fix is as error says convert to varbinary and replace highlighted code with above mention code fix.