Would you better off with the Nikon D40 or Canon xti? Probably not, particularly not for macro. You say you want a good macro lens. My personal advice would be do not try adding on so-called "closeup" lenses to the front of your main lenses. There are simply too many optical compromises involved and chances are you will be disappointed.
You have basically two choices from Olympus, the 50mm f2.0 or the 35mm f3.5. Both of these lenses are very sharp and can be used as everyday lenses as well (the 50mm is a great portrait lens). Sigma has several lenses out in 4/3 mount that are rated as macros, so you might want to look at them as well. The 510 has liveview--neither the D40 nor the xti have it--which is very handy for manual focusing when doing macros. And of course, the 510 has in-body IS--neither the D40 nor the xti have it--which can be helpful when doing some macros--which is to say, you'll probably want to use a tripod without IS for most genuine macro work, but under some conditions of exceptional lighting, you could probably shoot handheld with IS. Other features possessed by the E-510 and not by the other two include the only effective dust reducer and in-camera pixel mapping. About the only area the other two cameras outshine the olympus is high-ISO photography.
Right now the Olympus 35mm f3.5 macro can be had for $198.95 at Amazon. That's probably almost as cheap as a good set of closeup lenses.