The story of Friedrich Nietzsche's breakdown and eventual death has already passed into the stuff of legend, but I'll offer up for you the most accepted version:
Nietzsche, having spent his whole life stubbornly preaching the merits of what he called the will to power, stepped outside his Turin apartment one fine day to see a horse being beaten by its carriage driver. He cracked. Nietzsche threw himself between the driver and the horse, and he wrapped his arms around the horse, whispering in consoling tones.
Here's where it gets sketchy. Some people believe Nietszche uttered one thing in this intimate moment with the horse (e.g., "Sing me a new song! The World is transformed, and all the Heavens sing for joy!"), some people say he said something else, and some people say he never spoke a word again. This third claim can be discarded, as there are still records available of letters Nietzsche wrote and conversations he had after being admitted to the asylum where he'd die after 11 years of largely incoherent madness; these letters are curious reads, but only because the guy is nutty as a fruitcake, and not because he's continuing to make a contribution to the fields of philosophy or philology.
As if these details weren't sketchy enough, there are many medical professionals/historians who believe that Nietzsche actually faked his affliction. There is even a conspiracy theory about his sister Elizabeth "setting the whole thing up" in an attempt to discredit him. While this is clearly ludicrous, it does serve to illustrate the reaction of the many devoted followers of the philosopher, who perceived the tale of his breakdown as a compromise of the strength he had preached.
Nietzsche died in Weimar 1900. Indeed, the affliction itself is still a source of debate: though it's long been accepted to have been an "atypical general paralysis, brought on by tertiary Syphilis" (brothel visit in his youth), some medical professionals and even spiritualists, who seem to take great pleasure in the continuing analysis of this fascinating and high-profile medical-spiritual conundrum, believe that this diagnosis is unreasonable given the (11 years worth of well-documented) symptoms. Some theories have it that Niezsche's mental collapse was a spiritual awakening, possibly a metanoia or a Kundalini awakening.
What we do know, what it is easy and reasonable to conclude, is that the "epic significance of Nietzsche's madness" is that it was either the result of his rigorous lifestyle of study, self-inquiry, philosophy, and scholastic discipline, or it was a cause, a motivating factor, a biological precondition of his nervous system that he was staving off with so much of his impassioned rhetoric on the godless, moral-less world, a weakness that he attempted to turn back by convincing himself of the validity of the will to power, the Ubermensch, etc.
Indeed, the myth about Nietzsche hugging a church has a haunting resonance:
What is more harmful than any vice? - Active sympathy for the ill-constituted and weak - Christianity.
I would ask Nietzsche, What is a more explicit manifestation of weakness than to deny the receptiveness of the human mind to the world, to deny one's knowledge of the suffering of the horse, because one cannot handle it? Sympathy is no contemptible choice, it is neither contemptible nor choice, but rather a natural reflex of one's humanity, a receptiveness to the world that exists in all humankind, though he may try and fail to curtail or deny it. It is in such a way that humanity is connected to all the things it comprehends. And more sad and limiting than any other vice is it to deny what one necessarily feels because one is either too terrified of the truth or too weak to know and deal with the suffering of the horse, the suffering a human being vicariously endures proportionate to his knowledge of it.
Humanity pays for the fire of comprehension it has stolen from the gods by enduring the pain of all horses, by having access to all suffering. So Nietzsche vehemently denied; so Nietszche learned.
To omit sympathy from one's repetoire of feeling is to deny to understand. Nietzsche paid for his hubris.
Quote from R. J. Hollingdale's translation of The Anti-Christ, 2.