I have one. Hacked it to run XBMC, and while that worked fine on it there were some major features missing. Mostly lack of softsub support with hardware accelerated video, and the ATV2 is not powerful enough to run HD video in software.
That said like other Apple products it's great when used as designed and in conjunction with iOS devices. Netflix runs great on it, as does MLB.tv, but what really seals the deal for me is using AirPlay to just send video on my iPad (and now my new iPhone) to the TV. This is an excellent way to show video to friends. Also since I like to listen to podcasts in the car and at work I get the video feed using
Downcast and then if I'm at home catching up I use my iPad and ATV2 to watch the video.
It's not perfect, but I've yet to see something that is. So I wouldn't recommend it if you're looking for a single box solution that does everything. HTPC is still your best bet, but you'll certainly not get one for $99.