Kameo is the best game for that N64 Adventure game flavor... it's an excellent game as well
Enchanted arms is like Final Fantasy type game... it's got a really cool battle system to, but if you don't like that type of game you wont like this one.
Oblivion is an amazing game, it's definitely an RPG though, it break the mold enough that people who might not normally like this type of game will enjoy it but if you don't like RPGs at all you might want to avoid it... it's VERY in depth and expect to spend over 100 hours on it.
Ninety Nine Nights is an excellent Action/Adventure game IMO... may people pass it up because it can get repetitive, but having watched my GF play through, it looks like a superb game. I've heard it described as an "rpg lite" which I think is quite suiting...
Dead Rising is another awesome Action/Adventure game, very adult oriented though, as it deals with zombies but it's got lots of humor, fantastic gameplay and the story is very well written.
GUN is another wickedly fun Action/Adventure game... another one that I would call an RPG/Lite. IMO it plays like a watered down version of Oblivion, with multiple paths and sidequests, the differences is GUN concentrated more on gameplay and Action where Oblivion concentrates more on the storyline, and rpg elements like depth of customization. The graphics in GUN aren't as good as some other 360 titles, but it's still an excellent game and worth picking up
As for Xbox 1 games... there are lots of games you could look for
Fable is an excellent Zelda-like game, you also might want to check out the games on the Odd-world series as well as Conker and definitely check out both Psychonauts and Beyond Good and Evil... both fantastic adventure games. Not all of those games are Backward Compatible though.