I think the confusion is because you don't understand how powerful (or lacking in power) nuclear bombs really are. We dropped 2 on Japan and the only reason that ended the war was because they didn't want us to drop more on them, not because it "destroyed their army" or anything.
In fact, the bombs themselves didn't even completely "destroy" the cities they were dropped on. Sure, they damaged them greatly, but both cities were still there and still had massive populations (albeit a lot less than before - which is like in Civ 4 going from size 20 down to size 13 or whatever).
There was a lot of propaganda during the Cold War about how "scary" nuclear bombs were and how they could destroy the entire United States or the Soviet Union, but that's not true. Yes, nuclear bombs could have killed a lot of people in the really populated cities like New York and Moscow, but overall it would take a LOT of nuclear bombs to affect an entire country the size of either USA or USSR, and especially a LOT for either side to "destroy" the other - not just a handful as they made it sound like.
So I think Civ 4 captures this well. If you drop a nuke on someone you do weaken the one city you dropped it on, but it's not the "end all be all" that mid-20th century propaganda made it out to be. If American forces had tried to invade and fight in Hiroshima after the bomb was dropped, they would still have faced stiff resistance from local military units, just as in Civ 4 you will still have to face the Stack of Doom occupying the city even after you've dropped a nuke on it. The stack will be weaker, but it'll still be there... just like the Japanese Army was still there after having 2 nukes dropped on them.
So basically, nukes seem "weak" in Civ games because nukes ARE "weak" in real life. Propaganda has made them seem worse/scarier than they are, but the fact is, it would take a LOT of nuclear bombs to do the kind of damage you seem to think just ONE should do.