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.