But think about it, some of the best games ever have the most ridiculous and/or cliche plots...Those games are good not really because of their stories, but because of gameplay and presentation.
A decently written video game adaptation will still end up relying on the video game's story as a reference. The trick is, finding the right story to tell and doing something new with it. Silent Hill was pretty close to hitting this balance. Correct me if I'm wrong, but Silent Hill is the most critically acclaimed video game adaptation so far.
-TNC
Critical Reception (Taken From Rottentomatoes.com)
Silent Hill: 28%
Resident Evil: 33%
Resident Evil: Apocalypse: 21%
Resident Evil: Extinction 26%
Final Fantasy: The Spirits Within: 43%
Lara Croft: Tomb Raider: 19%
Lara Croft: Cradle Of Life: 23%
Mortal Kombat: 20%
Mortal Kombat: Annihilation: 4%
House Of The Dead: 4%
Alone In The Dark: 1%
Doom: 19%
Street Fighter: 27%
Double Dragon: 0%
Super Mario Bros.: 6%
D.O.A.: 34%
Actually, it appears that Final Fantasy: The Spirits Within is, critically speaking, the greatest video game adaptation of all time (which is a really, really sad accomplishment) with an earth-shattering 43%. I would say though that due to the fact Rottentomatoes doesn't have a large sampling of reviews that predate the late 90's, Mortal Kombat may have the edge. I'd guess it'd be somewhere around the mid 40-50% level. Regardless, as the facts show, Silent Hill ain't the title holder.
I do agree with you one hundred percent that most video game stories are cliched and shallow. Even ones with "epic" tales ala Halo or whatever are extremely simplistic when laid out, and really quite dull. Video game stories are simply clothes lines which stitch together playable scenarios. They are a constantly repeating pattern with little nuance or artistic flourish. As far as I'm concerned the only way to make a sucessful adaptation is to take the base, core concept and create an entirely new story around it (ala Mortal Kombat).