Meatwad... seriously... Core-Duo is THE WAY to go.
Even if you get the cheap one (E6300), you'll be VERY impressed.
I went from the same setup as you (Athlon XP processor, 512MB RAM) to a E6300 (1.7 GHZ, but dual core and new architecture) and 2GB DDR2... and the difference is absolutely ASTOUNDING!
I also went with a ASROCK DUAL-VSTA motherboard.. basically because the price is right and it has both AGP and PCI-E slots. I'm glad I did.
I spent well under $300 and got:
Intel Core-Duo E6300 Processor
ASROCK Dual-VSTA Motherboard
2GB DDR2 ram
160GB SATA hard drive
COMBO DVD/CD-R drive (IDE)
I was able to reuse my GeForce 6200 AGP Video Card
XP Home
Case/Power Supply
Wireless PCI LAN card
KB/Mouse/Monitor
Since the price of the processors have gone down since then, you might even be able to budget an E6600.
Also this sucker runs VERY, VERY cool... a good 15-20 degree drop in temperatures, compared to my old AthlonXP. Also I've heard you can overclock the heck out of these things, but I have yet to try (Nor do I need to).
Good luck!