" SELECT name, xmlFields, MAX(accountId)
GROUP BY name, CAST(xmlFields as varchar(8000)) "
where xmlFields is my ntext fields
but I getting an error message:
Error 8120: Column 'xmlFields' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The message is correct - xmlFields is in the select list but not in the GROUP BY clause. In the group by clause you have another column entirely: CAST(xmlFields as varchar(8000) is not the same as xmlFields
BTW - my hope is that is enough of a hint to guide your thinking to the solution - always better than just being handed it