Yeah, it can be used as a sound source. It's the only one that does with the potential of 12 voices, too. Not bad at all, considering what's (not) available in that world.
The "Buffalo Head" cartoon used a SX64, a midi interface through the cartridge port, and the program M64. Getting M64 from the internet to the SX64 was a serious science project... ended up somehow sending the program from my mac using a MIDI Sysex dump. Lots of fun, but only would want to do that once, you dig. 
You can use MSSIAH via MIDI, and it supports up to 2 SID chips. It also does samples - that you dump over MIDI sysex...
As for the Amiga most software just used Paula as a sample playback device. The synth capabilities were hardly used.
Older versions of Med and possibly Octamed support the synth functionality (IIRC they were called chip sounds).
Another program worth looking for is Aegis Sonix. It doesn't support MIDI but it is a proper synth.
For playing normal samples over MIDI there's a program called MidiIN that I believe supports normal 8bit Paula sounds. I haven't used it (I did download it but never installed it yet...)
I got it for much the same purpose as you - Playing 4 channel audio over MIDI (I also just ordered a C64 & MSSIAH).
There's a thread here about MidiIN here:
http://www.amiga.org/forums/showthread.php?t=53999