I bought a N64 ONLY for DOOM 64 as well, then I ended up HATING the controllers so I dumped the system, and kept the cart and added it to my MASSIVE collection of DOOM memorbilia.
A couple of other titles that were definatly kool were:
Rayman 2 (better on the Dreamcast though)
Mario 64
Zelda OOT
Paper Mario
Yoshii's Story (short and simple but a MUST for any Yoshi's Island fan)
Gex
Banjo K and T
F-Zero
Super Smash Bros.
Forsaken
Duke 3D
Daikatana
Mario Kart
Diddy Kong Racing
Starfox
Quest 64
Megaman Legends
Earth Worm Jim (what can I say I like toilet humor even at the age of 29).
Batman Beyond (just kidding this game blows ass, though the cartoon rox)
I would like to say both Castlevanias but, well you know.
Starwars anything even if it sux

Killer Instinct Gold
Mario Party
Super Lesbian Quest
Ogre Battle 64
Quake II (it's not the same as the PC, worth a go)
Resident Evil 2 (if only this were Alone In The Dark 1)
Shadowgate 64
South Park (both games)
Tetrisphere
Zelda Master Quest
Turok (not NEAR as good as the XBOX one, but hey they are FPS')
Vigilante 8 (once again, EVERYTHING was better on Dreamcast)
Impossible Mission
And the final two which I doubt we'll ever see run on the XBOX, but who knows I've not even tested them yet:
Conker's Bad Fur Day
Perfect Dark
So what Public Domain roms are good...
...wait new topic!!