In response to a thread on the XNA forums, JunkMailer (aka Leaf) put it far better than I had so far:
Just because the content pipeline pre builds content doesn't mean you can't be flexible at run time. Each mod could be pre-built and then at run time you select which assets to load based on the current mod. Ok, so to build a mod you would need XNA GSE but that's a fairly small issue (have you seen the size/complexity of the Half Life 2 SDK?). If you have a run time XML format for describing mods then it wouldn't take much to create a build time file format to control the building of the mod's content. This could then be run outside of VS using msbuild.
The fact that you can add content into your VS project and have it build at the same time as your code is a useful feature but it doesn't mean that your content is set in stone afterwards. When you use the content manager to load an asset, it just looks for a .xnb file to load, it doesn't care when, who or what built it. Actually, it would be pretty easy to derive a new content manager that looked in two locations for each asset; once in the mod folder and then in the default game folder as a fall back. That way people could easily override content without even having to explicitly say what content they had changed.
Give the content pipeline some love.
Thanks such a fantastically quotable quote. Also, one thing he didn't mention is how one could point end users to the XNA Content Builder (a tool that I feel Microsoft should just buy from those guys and provide with the redistributable) for further content pipeline building.