The Civ 4 Beyond the Sword (now like 10$) free mod Fall from Heaven 2 (available here:
http://forums.civfanatics.com/showthread.php?t=171398) and the mod-mod for FFH2 Masters of Mana (
http://www.masterofmana.com/) both turn civ into something like an rpg with very different tactics than standard CIV (assassins, magic) and with Masters of Mana, global terrain shaping spells.
You get national and religious heroes who level up rapidly compared to normal troops, and permadie if killed, and MoM ads a separate magic research tree, so your leader improves over time. MoM further pushes the rpg angle by keeping hard troop limits at around 20, so you have to work on leveling a team of chosen troops by hunting orcs and killing dinosaurs, exploring dungeons and similar hero stuff instead of stack-of-dooming grunts like in normal civ.
In FFh2 you could already ascend to be the god of winter and freeze the landscape (as the Illians), or summon an OP angel race into one of your own cities, fueling them with the souls of your honorable dead (as anyone) but in Masters of Mana this was war 101.
My favoritest game of it involved conquering a desert sun-worshipping people, and turning their entire land into a flaming hell with a ritual, only in order to provide a buffer from my friend's rapidly growing swarm of ice demon locusts.