Its not easy to tell a good story. Not every game can have the amazing depth, pacing and impact the story of Xenogears had. stories in Heavy Rain, COD4, Half Life completely pale in comparison to Xenogears. One of the main issues with game stories is the writing, several games that have a "so called" focus on story have bad writing which makes the impact of the tale they want to tell go down. Then there is the issue of pacing, how to tell a compelling and interesting story while taking into account pacing of the game.
Many games have done this well, they include Xenogears (as mentioned above), Final Fantasy VI, Xenosaga Episode I and Xenosaga Episode III. There are many more but those games had complex stories to tell and they did it very well. Uncharted 2 is an example of how important pacing is in a story, the story itself is nothing that great, but the way its paced is fantastic, you feel excited many times and it never feels too long or too early when the next plot event takes place. The Legacy Of Kain series had similar strengths, coincidently Amy Henning worked on both Legacy Of Kain & Uncharted.
