AND EXISTS(SELECT 1
    FROM
     (SELECT DISTINCT
       F_ALIAS,
       F_DATA_CODE,
       STUFF((SELECT ', ' + A.F_DATA
         FROM T_REP_DATA A
         WHERE A.F_ALIAS = RPDS.F_ALIAS
          AND A.F_DATA_CODE = RPDS.F_DATA_CODE
          AND '1:WVTST{PROD006' = A.F_ALIAS
         FOR XML PATH(''), TYPE).value( '.', 'NVARCHAR(MAX)'), 1, 2, '') F_DATA
      FROM T_REP_DATA RPDS
      INNER JOIN
       (SELECT DISTINCT F_CUSTOM2
       FROM T_MSDSTYPES
       WHERE (F_CUSTOM2 IS NOT NULL AND F_CUSTOM2 != '')) MT ON MT.F_CUSTOM2 = RPDS.F_DATA_CODE and RPDS.F_ALIAS = '1:WVTST{PROD006') RPDS_CUSTOM2)    
    WHERE RPDS_CUSTOM2.F_DATA LIKE '%MANU'

the above part in our original post only taking more time.please give some suggestion to rewrite the query.