I've always liked the plots of the Shadow Hearts games, what with them linking in history, mythology and its own plot quite nicely. But I'm a bit of a history nut so it appeals to me.

Otherwise Banjo-Kazooie had an awesome plot. A bear kidnapped by a witch so she can put the bear into an evil machine that will suck all her beauty and put it into the witch, making her look like Victoria Beckham circa 1998? Fantastic.