SQL Server 7.0, SQL Server 2000, MSDE 1.0 and MSDE 2000 all run on the Windows XP OS but some of the SQL Server services will be affected by SP2. Have a look at this Webcast for the skinny.
MSDN Webcast: SQL Server and Windows XP SP2 - Level 300
Basically certain services are going to be turned off and the firewall is going to block inbound traffic to SQL Server running on XP.
For a FAQ on this, goto FAQ: How Windows XP Service Pack 2 (SP2) Affects SQL Server and MSDE