I guess the problem is that the story is so straightforward. There's nothing really to expand on without tarnishing the cliche, and so you just end up focusing on the mechanical aspect of braving the castle/traps & beating the dragon. At that point, you might as well play a cooperative video game or board game. I guess it could work as a one-shot adventure.
Also, of course Super Mario doesn't count, as it is (or started as) a parody instead of being a genuine take on the story:
- The noble, armoured knight, armed with his magical sword & ancestral shield, riding his wartrained horse, defeated the evil fire-breathing dragon & his army, to save the fair princess of the people.
** compared to **
- The Italian, overall-clad plumber, armed with some random flammable plant & heavy boots, riding his gluttonous dinosaur, defeated the evil fire-breathing turtle & his army, to save the fair princess of the mushroom people.