Well, I don't use Xbox live myself, but quite a lot of the stuff these days gets downloadable content. Could be anything from a new level, new character model, map packs, in some cases a full blown add-on pack, though the smaller content packs are the majority at the moment as far as I'm aware. So it's moving towards the whole add-on end, but not in the user made stakes as yet.
Personally I doubt we'll ever see user made ones for any console, not since you need a licence to develop for them.
Haven't looked at Live Arcade but I hear there's some good stuff on it, but I'm not too familiar with the way the PS3 system works. The Wii has something similar to the arcade on live though.