Part 20 of XML Workshop shows how to generate an RSS 2.0 feed using FOR XML with EXPLICIT. In one of the previous versions we saw a different version of the TSQL code that generated an RSS 2.0 feed using FOR XML PATH. Using FOR XML PATH is much easier than EXPLICIT, however, it is available only with SQL Server 2005 and above.
The FOR XML EXPLICIT approach will work for both SQL Server 2000 and 2005/2008. You can find the article here.