In response to Lowds, The Scarecrow had a good story, but it petered out in the two follow ups. And since my other post was probably largely ignored since it was the last post of the page...here it is again. Fuck off reposting it pisses you off.
---------
There are plenty of great examples: Mindcrime, TDH's Acts, Streets, Amaran's Plight, Arena's The Visitor and Contagion, Henning Pauly's Babysteps, Dominici's Trilogy (not joking), Haggard's Tales of Itheria, and Clutching at Straws are probably as good as it gets.
There are several other good ones: Falconer's Scepter of Deception, Haken's Aquarius, the Rhapsody of Fire catalog (if you can get past the poor English at points), Sy X's V:The New Mythology Suite, fuck it too many to list...I know I'm leaving a ton off.
Blind Guardian's Nightfall on Middle Earth is probably the best adaptation of a well known story.
The Avantasia trilogy started off as good music and story but the story was lost on me on the last two albums.
As SDN said, Into the Electric Castle is pretty bad story wise, but the hippie took a two car accident and made it a 26 car flaming pileup.
I don't feel like going through all of mine. PM if you want a rundown.