Not sure about 2005, But XSDs can be used to exuecute queires on the SQL Database. So they can be used as alternatives to direct SQL Queries or SQL Queries in stored procedures.

The advantage is similar to SPs, where in we can jsut modify the XSD if we need to modify the data that is returned instead of changing your whole code.