Yep, this is typically how these things tend to work at most clubs (including the traditional heavyweights).
It's an ex-player who is appointed to the position (with first-hand knowledge of the club and its principles, as well as certain people in the inner circles), like Txiki Begiristain for that Barcelona team, Michael Zorc for Klopp-era Borussia Dortmund, Santiago Solari at Real Madrid right now or Monchi for Sevilla. Or an an executive plucked from a smaller club or someone who rises through the ranks internally, like Beppe Marotta and Luciano Moggi for Juventus or Michael Edwards for Liverpool. Louis Rocca is probably the closest we've had to a great Sporting Director type figure and he started out as a tea boy at Bank Street.
Very rarely does a club succeed with the appointment of a ready-made “superstar” Sporting Director, as the context of their initial success can be difficult to replicate in a new environment with different moving parts and a different set of demands. Having the right ideas, intuitive grasp of deductive principles (a sixth sense for football, as it were), eye for talent (players, coaches and executives), scrupulousness and interpersonal skills are the foremost prerequisites, and they are not directly proportional to top-tier experience.
With regard to Jason Wilcox, he might or might not succeed as Manchester United's de facto Sporting Director (officially only a Technical Director for now). Time will tell. But that will not have much to do with his lack of top-tier experience in a similar role. Silvio Berlusconi plucked Adriano Galliani from Serie B club Monza, Galliani brought Ariedo Braida along with, and they appointed a Serie B coach in Arrigo Sacchi. When Sacchi left they appointed a former player in Fabio Capello. None of these were vastly experienced in the top tiers at the time, yet they delivered multiple historically significant teams.