Essentially, water is pumped through a "waterblock" basically a block of copper or aluminum, designed to fit where a traditional air cooled heatsink would go on the CPU. It then goes to a radiator similar to the one in the front of your car (although much smaller) which has some fans on it to cool it. The advantage is that the fans can be larger and therefore quieter, while achieving better cooling.
Not that I claim to know for sure... or that I've tried it, but I suspect the force required to get the water through a full size radiator would break the hoses or waterblock or something.