What was the point of demoralizing your enemy if there was never going to be any actual fighting? Xindi weren't about to start a war, they were planning on wiping out humanity in one swift move.
Is there ever a point to it? If you hate your enemy, you want to strike at them, hurt them. That desire can trump good sense, as history clearly shows. The Xindi had been taught to hate and fear humanity, so they wanted to terrorize us. It's the same as the point to torture, I guess. Countless studies have shown that torture is not a reliable means of gathering information, and in fact is counterproductive to that end; either torture victims will lie to end the suffering, or the trauma can actually impair their ability to remember information accurately. Yet governments still inflict torture on prisoners because they want to subjugate and abuse the people they hate, or just simply because it's a power trip.
Human military decisions are not always rational. One could argue that if we were entirely rational, we'd rarely if ever wage war at all, because it's a really wasteful way to get anything done and the damage it does frequently outweighs any benefits. And more than a few battles and wars have been lost by sheer stupidity or arrogance. Think about the armies in WWI that were mowed down in ghastly numbers because European martial tradition and pride dictated that they stand and face the enemy openly, which left them as sitting ducks against the new technology of the machine gun. And it wasn't just in WWI; the Soviet advisors sent to China to assist the Communist rebels insisted on employing the same traditional, "honorable" tactics instead of the guerrilla warfare Mao advocated, and as a result, the Chinese Communists were virtually exterminated. Mao could see how irrational the Soviets' military tactics were, how completely inappropriate they were to the situation, but they couldn't see past their pride and cultural blind spots, so they failed.
Now look at the Xindi Council, and at its most militant members, Commander Dolim and the Insectoids. They were pretty fanatical, not exactly prone to reason and caution. True, the councillors from the other three species were there to keep them in check, but all they needed was to convince one of the three to go along with them. So it's really rather understandable that the Council's military decisions could simply have been bad ones, at least occasionally.
And I think Sphere builders, with their ability to inspect multiple timelines and all the possible outcomes, would have known that pulling a stunt like that could have jeopardized their whole game (as it did).
That's a good point. However, their timeline models seemed to shift only in response to things that had already happened in the present. And at the time the initial attack was approved, Future Guy hadn't yet intervened to alter events in Earth's favor. So the Sphere Builders' timeline models at that point wouldn't have taken
Enterprise's involvement into account, and thus they might've concluded that the probability of success was sufficiently high to allow the test.
