The way you have done the SELECT (SELECT...), (SELECT..) ... FROM forces those massive nested loop joins on 900K rows and the logical IO from those is stunningly high.

First, make sure you have a covering index on each of the correlated subqueries. But even that may not help you with all those 900K iterations.

If it isn't fast enough, I would switch to a CLR object to do the concatenation. That will probably be the most efficient. It also avoids not one but TWO scenarios currently where you can get the WRONG OUTPUT!! A) you have no order by in the correlated subqueries meaning they can put the concatenation in any order on output and B) XML "special characters" can actually BREAK the FOR XML processing (think <, >, etc).

You could even try a cursor-based solution to build the output of each of those correlated subqueries.