I've just discovered something neat...
After executing the following command in the terminal:
defaults write com.apple.Xcode PBXBuildSuccessSound /Users/sam/Music/Codewarrior\ Clunk.aiff
My XCode now plays the familiar CodeWarrior "metal stamp" sound when my build finishes! Of course, you'll need to replace the path with a sound file of your own. I used Audio Hijack Pro to extract the clunk sound from a Codewarrior, but possibly it's out there on the net somewhere.