SQL Server XML - Making a List (and Checking It Twice)

It seems like the most frequently-asked question on the MSDN T-SQL Forum is how to put together a column that contains a list of some kind. For example, take a look at the Northwind database. There’s a Categories table and a Products table. We’d like to produce a list of all Categories and, for each Category, produce a column containing a comma-delimited (and sorted) list of ProductID’s that are discontinued.