Just recently got a chance to play with Steinberg's new Hypersonic sound module. Works just like a hardware sound module such as the Roland XV5050 (which I own and love), except it is software. I was quite impressed. That said, if you are looking to build a small arsenal of truly realistic sounds, there is, in my opinion, no perfect all in one product. For this purpose, I would advise you to purchase a virtual sampler (Gigastudio, Kontakt, Halion) and buy instrument specific sample CDs. Actually, the libraries that come with each of these products might give you enough sounds right there.
Hope this helps