In a well designed system, any system should reinforce the intended set of playstyles or general things to happen in a session. Human brains are designed to optimise a reward-seeking behaviour, so if the characters in our game get rewarded with XP for doing something, we are naturally encouraged to do that thing more to get the same reward. If you get XP for killing monsters, you will seek out more monsters to kill, etc.
Below is a large compilation of various XP systems that I tried arranging into a somewhat cohesive whole. However, because sometimes the systems have odd edge cases with how XP is given out, some of them might not fit as neatly. I've also tried briefly explaining any important mechanics relating to XP in any given system, but by no means is it an exhaustive explanation - that would take too long. Finally the various sections feature an insight into what the given XP systems might encourage from the players and the game as a whole.
Goal-Based XP - Dungeons and Dragons, Stars Without Number
Goal-Based XP systems are very focused on characters accomplishing set goals.
Dungeons and Dragons is a staple when it comes to Goal-Based XP rewards. Most of you should have come across this infamous XP table for how much XP to reward based off what sort of adventure the party is having:
You use it to figure out how much XP to give out per encounter the party completes, and you multiply it by factors such as the number of monsters and so on. It's very cut and dry this way. In short - you have your goal ("a monster is attacking you, stop it") and you get XP for completing your goal ("kill the monster").
The Dungeon Master's Guide also gives you a few alternative ways to reward XP - for completing noncombat challenges, for reaching significant milestones, or per-story / per-session rewards. All of those are a variation of "Goal-Based XP".
Stars Without Number features a similar system. By default, you get flat XP per session, but you can change it to getting XP for achieving a personal goal, completing a mission, or more interestingly - loot. The group can decide they would tie their character progression based on how much money they get, or how much money they "waste" on things other than themselves.
All in all, Goal-Based XP encourages the players to think of the game as a series of challenges to overcome in a vein of more modern computer RPGs - "here is your quest, do a quest, get reward". It is a fairly straightforward system, but it doesn't encourage much nuance - you're not really rewarded for having an introspection as a character, having some meaningful interactions or the like.
XP by Practice - Cyberpunk, Call of Cthulhu, Mouse Guard
A different approach to gaining XP is to reward a player for using a particular skill. A few old-school and more modern RPGs use this system.
In Cyberpunk 2020, characters have a list of skills, stuff like Rifle, Drive, First Aid, Personal Grooming, etc. When a character uses that skill in a session and succeeds, they mark it, and at the end of the session the GM awards the player skill-specific XP (called Improvement Points) based on how critical that skill was to the character or the party this adventure. If you accumulate enough Improvement Points for that skill, you level it up.
While you will get the bulk of your IPs from using a given skill, you can also get some basic IPs from studying or training that skill as a way to get at least a few ranks in a skill in a safer environment.
An interesting paradigm my old GM pointed out in regards to this system is that you tend to get more IPs the more you fuck up a mission (while if you succeed a mission you get more cash). Surviving by the skin of your teeth by driving away action movie style is more important to the character than driving away from a heist that went smoothly, so it nets you more IP. If GM is generous, if you are the last person alive from the party, you count as the entire party, ergo netting you even more IP for critical successes.
A similar but less bombastic system is in play in Call of Cthulhu. Whenever the character uses a skill successfully, the GM can prompt them to mark it. At the end of the session you roll for every skill you have marked - if your roll fails (aka - more often for things you are bad at, and less often for things you are good at), it goes up. Simple and straightforward.
Contact has a system where you get a flat XP (based on your int) for using a skill, whether it's a pass or fail (critical successes double the XP, critical failures net you a 0). When you accumulate enough points, your skill goes up. You can train / study for another flat XP gain.
Mouse Guard adds a twist to the formula - you track how many times you have succeeded and failed in a given skill separately, and you have to get both of those numbers high enough in order to level up that skill. During the GM turn you have to pretty much go with the flow as to whether you can succeed or fail, but during the players' turn, you can influence your odds by doing harder or easier challenges to get that specific pass / fail you need.
All in all, XP by Practice encourages the players to be active - to be the person that drives, shoots, talks, gets into trouble, etc. The more rolls you make, the better your character becomes. While this solves the issue of players being passive or not wanting to take initiative, it can be a dangerous tool if you have an attention hog in your party. The system encourages you to be active all the time, even if that would be hogging the spotlight from someone else. Even if that is not an issue in your party, it can encourage hyper-specialisation - "you are the best healer, therefore you should roll to heal. Because you rolled to heal, you become better at healing". You can break up the monotony by forcing characters into a situation where they have to roll out of their comfort zone, but if you are behind on some skill, you have to put in a lot of effort to catch up.
Character Growth XP - Apocalypse World, The Veil, Star Trek Adventures
Systems that use Character Growth XP tend to focus more inwards. The adventure of the session is a way for the player characters to reflect on themselves and the characters around them. These XP criteria can get a bit complicated, so please bear with me.
In Apocalypse World, you get XP for rolling a highlighted stat (stats that best highlight the character), but more importantly - for getting your relationship with another player character (the relationship is called Hx) to "roll over and reset" (either by being very positive or very negative). You gain relationships with other characters either by causing that character to get hurt, or at the end of the session by selection a character that "knows you better than they used to". You lose relationship if someone "doesn’t know you as well as they thought". You also get XP for being manipulated by another PC, or by a few other moves. All in all, the system revolves around relationships with other characters and moving them up or down, even if it has a few other things going for it.
In The Veil, you get a point of XP when you attempt to do something for your own benefit and fail, but also more importantly for things revolving around your Beliefs. A Belief is what it says on the tin - a belief that drives your PC. If your Belief is tested, you get an XP, if it gets you into trouble, you get 2XP, and if your Belief is erased, resolved or changed after being tested - you get 3XP. The system thus encourages you to have Beliefs that would be challenged every session so you can see what ideas will persevere.
Star Trek Adventures is another big and a bit complicated system. First, the character can earn Normal Milestones for challenging their Values ("duty above all else", "we will persevere", etc.) and Directives ("The Prime Directive", "Seek Out New Life", etc.), using those two in a positive or negative way, or getting hurt by an attack. These encourage you to test and question your beliefs and to better yourself - the reward for getting Normal Milestones is usually a shift in focus and replacing your Values, rather than adding more things onto your character.
Characters that were particularly prominent during an adventure receive Spotlight Milestones. Those are used to further shift the focus of your character, but also to alter your Ship's stats as well (refocusing it based on the major events of the adventure). Every few Spotlight Milestones you get an Arc Milestone, which allow you to improve yourself or the ship (by adding points, rather than shifting them around).
Similarly, your character can also gain and lose Reputation based on their actions. They gain it for acting according to orders and Directives, preventing combat, establishing an alliance with an enemy, saving lives and acting above and beyond the call of duty. You lose reputation for challenging a Directive, personnel under your command getting killed, resorting to lethal force without cause and taking unnecessary risks. Reputation is used to gain ranks, privileges and responsibilities in the Starfleet and it is a mostly roleplay progression.
All in all, Character Growth XP is pretty useful when you want the game to focus on the characters at play. Everything loops back onto them and their relationships with themselves and each other, and the adventures of a session are useful mostly when they let the characters have those moments of introspection.
Cinematic XP - Broken Worlds, Fellowship, Chronicles of Darkness
Systems with Cinematic XP put an emphasis on things you would see in a movie or TV show.
In Broken Worlds, you can get XP by using a Train move (which is required to actually level up), but more importantly you will be getting XP at the end of the session for "failing in some regard", "exposing yourself to danger, cost or retribution through your actions" and "progressing your story in a meaningful way". Those three things you could easily see in any TV series - you want the characters to fail because that builds more complicated stories. You want them to expose themselves to danger, since playing safely is boring. And finally, you want all of that to have a meaning to the overall story.
Fellowship has a similar system, although it could just as accurately be described as Goal-Based XP. At the end of the session, you progress if you "saved or protected a community in need", "strike a blow against the Overlord and their minions" and "learn more about the world and its people". The system fits the narrative structure of the Fellowship where you're supposed to be on a somewhat serialised quest like Avatar the Last Airbender.
Finally, there is Chronicles of Darkness, a system with many ways of earning experience. Firstly, a character can get XP for fulfilling or making significant headway towards an Aspiration. Aspirations are either short-term, or long-term things you as the player want to happen with the character. This distinction is important - Aspirations are a Doylist choice by the player, not the character ("my character Watson doesn't want to get hurt, but me, Doyle the writer want him to get into trouble"). For Vampires, some of their aspirations revolve around the vampiric world, and some around the human world instead of being short-term and long-term.
Then, you earn XP for dramatic things that happen to the character - when they get hurt badly enough to be in danger, when a Condition ("Guilt", "Fugue", "Spooked", etc.) impedes them or gets overcome, or when you make your failure a dramatic failure. These generally denote some serious complications the characters might face.
CoD gives you a standard 1 Beat (partial XP) automatically at the end of the session, any dramatic scene can reward additional Beats at storyteller's discretion, and similarly exceptional roleplaying, tactics or character development might merit another Beat.
Another major source of Beats is risking a Breaking Point - when you challenge what it is to be human, when you are faced with supernatural forces beyond your comprehension or the like (appropriate to the supernatural type you are playing), you get a Beat. The Breaking Point check usually also results in a Condition that gives more Beats.
Some supernatural splats also reward different kind of Beats. Playing a Mage you can earn Arcane Beats for following your Obsessions (Mage Aspirations), dealing with consequences of your Magic (Act of Hubris, Paradox), being tutored or tutoring others, and encountering supernatural creatures. Playing a Demon nets you Cover Beats for living under the radar, acting according to your Cover (fake identity / skin you wear to hide in plain sight), or for forging demonic pacts.
This long long list should about cover most of the Chronicles of Darkness splats and systems.
So all in all, Cinematic XP is focused on creating "cinematic" moments in your sessions - moments of high tension, high drama and high consequences. Your story might be one of beating up baddies wuxia style, being the hero that rises up against an evil Overlord, or of a film noire detective getting beaten up on the curb. Whatever it is, it is your story to tell.
Hodge Podge XP - World of Darkness, Exalted
For the sake of completeness and to contrast against the Chronicles of Darkness, lets have a look at Old World of Darkness (Vampire the Masquerade, etc.) / New World of Darkness (Chronicles of Darkness 1st edition - Vampire the Requiem 1st ed, etc.). The system presented by these systems is a bit hard to categorise. You get XP for completing a session, for your character learning something new, for roleplaying your character well, and for acts of heroism. At the end of a story arc, you get additional XP for succeeding at the adventure, for surviving dangerous situations, and for displaying wisdom and coming up with clever plans. In nWoD, you would also get bonus XP when a Flaw you took would impede your actions.
All in all, it's a bit of a Hodge Podge when it comes to categorising. Some are for Character Growth, some are for Cinematic, and some could be considered Goal-Based. The system generally seems to just give you rewards for things you are expected to have in an RPG, without any special focus.
A bit of a more focused Hodge Podge XP system can be found in Exalted. In the 3rd edition, you get a flat mount of XP per session. You also get a bonus Solar XP for two things - Expression and Role Bonus. Expression Bonus comes in when you are impeded by a Flaw, reveal something about your character by expressing / supporting / engaging their Intimacies or being challenged, endangered or harmed while protecting or upholding your Intimacy. Role Bonus comes into play when you cede your "spotlight" and let another character shine in their Caste, or by doing something impressive in accordance with your Caste.
Exalted's Hodge Podge system, despite drawing from Character Growth and Cinematic systems, works much better than World of Darkness since it is used to highlight the key mechanics of the system. Intimacies are important in the system, so you get rewarded for engaging with them. Castes are important to what the characters are, so you should express yourself with them. Sharing the spotlight is important, so even if you don't get to shine, you still get rewarded for not interfering with someone else's moment to shine. It's quite coherent in its design.
Conclusions
Well, that was a long and varied list. If anything, this goes to show how varied the RPG experience can be. You can try to draw a number of conclusions from the comparison.
First of all, if you are designing or homebrewing / homeruling a system, take a moment to think about the XP system and see what sort of games and sessions it encourages. Are those elements congruent with the themes of the system? If so - great! If not - you might want to tweak them. XP system is like any other part of the game you're playing - a tool to help you tell the stories you want to tell. It's best when it encourages the playstyle and experience you want to get, not work against it.
Secondly, you should be aware of what sort of playstyle is encouraged by the game you're playing. Just like other mechanics and themes of the game, it will shape your play. Keep that in mind.
Thirdly, if you are playing a system where the XP system feels bad or like an afterthought, you should probably change it to suit your individual playstyle. In our game of Exalted vs World of Darkness we did exactly that - we threw away the oWoD XP system and made our own that encouraged us to keep looping back into the core themes of our game (it being a game set in high school, we were encouraged to engage with the high school and home life setting, despite being avatars of ancient demigods that fight vampires on the regular basis).
Finally, comparing the systems, it seems that mostly "XP by Practice" could be a detrimental system for an overall constructive play under the wrong conditions. All other systems can be made to serve your story just as well. You can mix and match elements from either to create the perfect experience for your game as long as you are aware of what purpose a given system serves. You want an XP system to reinforce your themes and other mechanics, even if you have to draw from different inspirations. That is fine.
All in all, your XP system will shape your game to a greater or lesser extent. Just like with any tool in your arsenal, make sure it is aligned with the vision of what you want your game to be.