By allowing SQL Server to return (and/or receive) XML you can build pseudo-web services which can be bound directly into XML and used on the client side.
The benefits of having XML at the client are well documented, SQL Server gives you an ability to deliver this XML very quickly.
By populating an XML DOM directly from the database you can have decent speed improvements over iterating through recordsets.
I've recently built a search engine which utilises an XML return and the speed of the process is stunning. Searching through an index of over 100,000 words in a SQL2K database the intranet page is usually rebuilt 0.05 seconds after the search button is hit.
By using
page -> ado -> stored procedure -> ado recordset -> page
I would not get this kind of speed or flexibility.