I think water is transparent because eyes first evolved in water. The range of the EM spectrum we detect corresponds to the spectrum for which water is transparent (absorbs the least). Had we evolved in mercury, we would think mercury is transparent and detect EM waves that pass through mercury.

Now why is it so?

Because being able to detect EM waves that pass through water can greatly help us survive through detecting the source of energy (sun) and avoiding predators (or finding prey/food). Why can't we detect higher energy wavelengths that pass through more dense material? Well first of all, some creatures do (e.g. Mantis shrimp). Second, not much of the higher spectrum radiation makes its way all the way to the earth surface. Third, high energy beams are difficult to handle by the receptor; they destroy you if you interact with them, and therefore the receptors (eyes) that detect them are expensive to make.

For more see Quora User's great (more detailed and complementary) answer. [I decided to update this answer as many more people read it than I expected, there was some hand-waving in my original answer that I tried to get rid of, while not changing too much.]