Pros1. Strongly-typed loading. Each asset is serialized as a binary asset, and as a result, asset loading is not only quick, it's type-safe. The larger and more complex the asset, the better this is.
2. Extensible. The content pipeline was designed to be extended in every which way possible. For most items, composite objects are going to be the the name of the game because a lot of the commonly built functionality is already embedded into the framework. If you need extra functionality above and beyond what is there, for most things it will be easy enough to add it yourself.
3. (Mostly) self-configuring. It's nice to be able to add an asset and (most of the time) have the proper settings already set on the importer/converter.
.XNB. Whoever had the bright idea to make everything have the same extension and have to individually rename assets inside the IDE needs to be strung up by their testicles and flogged in public. It is very common to have a model in a folder with the appropriate texture having the same base name (chair.fbx/chair.tga for example). The reason for this is because if you don't have the files in the same folder, most modelling software freaks out with the texture paths.
2. No included packed file system. True, you can subclass the ContentManager to make your own packed file system, but the time savings from loading a strongly-typed serialized file are more than offset by slow individual file access times (due to security lookups, etc.) For small projects, this isn't that big of a deal, but once you start getting into asset counts in the 4- to 5-digit range, it matters.
3. Not team-friendly. Bear with me on this one. Converting an .FBX file to a model, or a .TGA to a texture takes little to no time, but when you have custom asset importers to handle certain items (lightmaps, pathfinding precalculations, visibility), the time it takes to process these items increases by several orders of magnitude. We aren't talking seconds...we're talking hours EACH. One of the primary purposes of any asset pipeline is that this time cost is paid one time per iteration of the asset. With the current XNA Content Pipeline, each person who gets the asset is going to have to pay the time cost unless they use source control, which leads me to...
4. Not source-control friendly. Now, Visual C# Express Edition does not support source control, but you can still use an external source control solution. The nice thing is that you can eliminate the extra time costs via source control. The downside is that to get this time savings, you have to check in not only the asset and the compiled asset, but the intermediate "temp" file as well. Then to do an iteration, you have to check out all three files, do the build, and then check them in again.
5. Documentation still fairly weak. Just try finding the information you need to write an image importer inside the documentation...be prepared to delve through over a dozen help topics and even then you'll have to go through and do it by trial-and-error.
6. Plug-ins. Don't get me wrong, I love the idea of plugins. My major issue here is that they seem to have missed one of the strengths of .NET. If I create a control in a Windows Forms project, I compile and then that control is available for use in my toolbox automatically. The IDE uses reflection on the compiled assembly and lets me use the control appropriately. If I'm writing plugins as part of my game, why do I have to then reference the plugin DLL through a setting?
7. Still haven't found a way to hack it to work outside of Visual C# Express Edition. I will find a way...