Component video was the HD output from the original Xbox so had wide appeal. Outside of that yes, it was a niche solution in consumer video although huge in professional/broadcast world.
Your background with MS is showing...
I was never knowledgeable of gaming consoles, and my memory isn't serving me especially well with the stuff that I used to know something about. I just now spent a little time to refresh and augment my memory.
S-Video was first introduced in 1987 with chrominance and luminance physically separated and using the round 4-pin DIN connector, however this wasn't the first instance of physically separating chrominance and luminance. At the time when S-video was introduced it was natural to physically separate the video by chrominance vs. luminance because of the way color information was carried in the NTSC broadcast signal, which was the standard in Japan as well as North America. (And that method for carrying color was kludgy because it needed to be backwards-compatible with the broadcast signal format for B&W TV. A local oscillator was needed in the receiver in the TV to provide a reference signal; if I remember correctly, hue was determined by the phase difference between the local oscillator and the chrominance signal that was squeezed into the video band. A signal for synchronizing the local oscillator was transmitted in the interstitial between consecutive frames, similar to the vertical sync, if I recall correctly.) Of course S-Video was never considered suitable for carrying HD, however in consumer electronics it was the only alternative to composite video from 1987 to the late 1990s, a period of eleven years.
Component video made an early appearance in the form of RGB as found in personal computer video monitor connectivity, where the connector was originally the 15-pin DIN connector, aka IBM VGA. I don't know when component video using three RCA connectors was first introduced or by whom. Perhaps the three-RCA-connector method was used in professional broadcasting prior to the date when the first HDTVs begin showing up. But the first I recall seeing the three-RCA-connector scheme was with early HDTVs, in 1998 or 1999. HDMI began appearing in consumer video in 2003, however it was an evolution of DVI and there were some consumer HDTV sets with some variation of DVI prior to 2003. HDMI included provision for audio whereas with DVI, audio had to be carried on a separate cable, and of course HDCP (copy protection) was introduced with HDMI.
As for the Xbox, I should say again that I was never particularly knowledgeable of game consoles, but I learned a little in the quick bit of research I just did. When the first Xbox was introduced towards the very end of 2001, it had composite video output. Evidently it didn't have S-video output (I haven't confirmed this), which isn't surprising considering that it was, after all, a game console. Component video first appeared in the Xbox with the Xbox 360, in early 2005, at least a year after HDMI began appearing in HDTV sets. The Xbox 360 came with an unusual cable, a bundle with the three RGB cables, plus a cable for the composite video signal for backward compatibility, plus stereo audio cables, all bundled together using a single connector to the console. It isn't surprising that component video would have been used in lieu of HDMI because, for one thing, most existing HDTV sets were still component-video-only, and it was reasonable to anticipate that future HDTV sets would continue to support component video. There was no compelling reason to adopt HDMI at that point, and cost containment would have dictated support for just one or the other. Possibly, the decision by MS to use component video in the Xbox 360 gave HDTV manufacturers more reason to continue supporting component video longer than they otherwise would have. I don't know about that, but the direct reason that component video outputs continued to appear on cable and satellite receivers and on some DVD players was that many existing, early HDTV sets were component-video-only.
