With a Limited Account, you cannot install hardware or software, or change any files not assigned to your account -- for example, you cannot change C:\Program Files. You can upload and download pretty much anything. You can also install most browser-based applets, since they tend to be one-time-use-only Java programs.
Limited Accounts cannot reformat your hard drive (which is probably a good thing...) or update Windows or other software. But most software, including Windows, lets you automate updates, which will run in the background while you are using your Limited Account.
My own preference is to have an Admin account with the name of Maint, and a password set, on each of the XP machines that I support, which I only use when I'm installing hardware or software, or doing non-critical updates to software, including Windows, that does not get done automatically. Otherwise, I always use a Limited Account.
When XP first came out, there was a lot of software that would only work with Admin accounts, most often because it wrote in files in places other than the account's own folders. Some "poorly behaving" software still exists, typically kid's programs, so it can be an issue.