I did a show about a year ago of "story songs". Among them:

Gordon Lightfoot, "the Wreck of the Edmund Fitzgerald"
Loreena McKennitt, "The Lady of Shalott"
Paper Lace, "The Night Chicago Died"
Dennis DeYoung, "Desert Moon"
Harry Chapin, "Cat's in the Cradle"
Luther Vandross (or Karen Carpenter), "Superstar"
Billy Joel, "Scenes from an Italian Restaurant"
Chad Mitchell Trio, "Lizzie Borden"