Xbox Live’s @MajorNelson has released two of the new Xbox 360 system sounds so that you can use them on your mobile phone as ring tones, system alerts, message indicators or even your computer to replace systems sounds.
Xbox 360 power (.MP3) (.M4R) iPhone ringtone format
I think these would make perfect sounds to indicate a text message has been received as they are short and succinct. I am not sure they would work well as a ringtone myself but you could give them a try and see what happens.
Let us know how they work out for you.