Soldier of Fortune II is great. I think it is the most violent game I've ever played; but unfortunately it also has some of the crappiest graphics of any game out there. They need to make a sequel to this game with a seriously upgraded look.
Yeah, I almost blew it off at first due to the graphics, but I agree, it's a great game. I love it when you shoot one in the knee and his leg comes flying off, or one collapses against the wall and a little extraneous spurt of blood comes pumping out.
But I haven't yet seen a game that I would truly call gory or truly violent. It's all cartoon violence. Bang, you're dead.