The error message recommends turning it into a derived table format. Try this:

SELECT ISNULL((SELECT *

FROM (SELECT TOP 10

au.sex,

au.firstname,

au.middlename,

au.lastname,

am.email,

c.id AS objectid,

c.title AS objecttitle,

c.friendlyurl AS objecturl,

3 AS objecttype,

am.CreateDate

FROM aspnet_users au

INNER JOIN aspnet_membership am

ON am.userid = au.userid

INNER JOIN cameras c

ON c.userid = au.userid

WHERE c.indexboost = 0

UNION ALL

SELECT TOP 10

au.sex,

au.firstname,

au.middlename,

au.lastname,

am.email,

c.id AS objectid,

c.title AS objecttitle,

c.friendlyurl AS objecturl,

1 AS objecttype,

am.CreateDate

FROM aspnet_users au

INNER JOIN aspnet_membership am

ON am.userid = au.userid

INNER JOIN locations c

ON c.userid = au.userid

WHERE c.indexboost = 0

AND c.id NOT IN (

SELECT objectid

FROM emailssent

WHERE category = c.objecttype

AND emailid = 2)) Sub

ORDER BY CreateDate ASC

FOR

XML RAW,

ELEMENTS,

ROOT('user')), 0) AS records ;