I listen to Soundgarden a lot so I find this interesting. I assume you're talking about the album Superunknown (and a few tracks from Down on the Upside), because that's where the bass had a sound more associated with flats, where it tended to less distinct. I actually like that dark, low, tone, because the bass is there doing its job. It sits in the background, making for a whole new layer to the music; a hidden beauty that relatively few will ever perceive.
Ben doesn't stand out at all in the verses of Spoonman, and a lot of intricate playing is unclear in the mix of Head Down and Fresh Tendrils. But do you know the bass is there? Definitely. Being so low makes the bass cut through in its own way, which I enjoy. For example, in Let Me Drown and 4th Of July, when the bass first comes in, it's like a kick in the chest (In the latter though, did he even use flats? In the intro, the loud string squeaking and artificial harmonic make me wonder...).
I highly recommend you listen through many of the songs after transposing them up an octave. Due to the mountain of fundamental, the bass becomes infinitely more present. It's like hearing it for the first time and is undoubtedly a necessity when you're trying to learn those songs. You'll find that Ben really liked power chords, they're all over this album. (4th of July uses a repeated open C powerchord. As in, if you tuned to CGCF, you would strike the two lowest strings open. Yeah.)
While I don't listen to Soundgarden, I agree with your assessment in so far as the bass' desired effect in the music. I feel that too often bass players are interested in "cutting through" rather than sitting in that bass pocket within the rhythm section and providing the underlying support for the rest of the instruments.
I think it gets right to the heart of the matter regarding flatwounds and the application for them. Some bass players desire to "cut through" while others prefer to be that bottom support. Both are valid concerns.
Flatwounds aren't going to be the be all and end all, for every type of music, but what they do provide is that thick underlying support to the music in a way rounds don't completely convey, just like flats do not in the way rounds do.
I've heard it mentioned many times over my career and many times in this thread. In some cases bass players have problems being heard using flats. The problem is not as much one that is caused by the strings themselves, but rather the player's expectations of what the bass should sound like.
If one is expecting the brightness and that "cutting through the mix" one gets with rounds in let's say, more of a soloists idea of what bass should be, then no; Flats are not going to give you that and therefore no matter what you do or how you EQ you are going to feel like something is missing in your sound.
On the other hand if the player's expectations are that the bass is going to stand out in it's own designed, unique voice of being the bass instrument that it is, and you desire to give the music that underlying strength and support, then your perception of the bass' sound is going to match your expectations and you will not struggle with being heard and will not feel anything is missing because you know that what is missing from your sound, is being provided by the other instruments.
It's all about giving each instrument space in the music. Many round players want to dominate the music in that they expect the bass to not only provide the bottom to the rhythm, they also expect to be the dominating sound in the mid range where the guitars and other instruments shine as well. If one's expectation is for the bass to be that, then flats are not for you and one should use roundwounds and play the "cut through the mix" and "volume" game with the rest of the band.
I believe there is a psychological phenomenon at play here as well. I think many of today's players equate being in a supportive role in the background with being unimportant. As if the lead guitarists and singers get all the props, while the rhythm section is this "silent partner" that receives no recognition. I do believe many players overcompensate by overplaying parts in an attempt to prove how worthy their contribution is too. The "look at me!" syndrome, as if to say..."
If only I could get my bass to sound as bright and stand out like the lead guitars do, and if I play some flashy runs then people will notice what a worthy musician I am too."
And there is nothing wrong with feeling that way depending on the style of music played and what is important overall. Some music is inherently more "bass" forward. Most is not. The Stanley Clarkes, Vic Wootens, Chris Squires and Geddy Lees of the world do not want to be in a supportive role, nor should they be. But most music is better served by fulfilling the role that each instrument needs to fill, much in the way an orchestra successfully creates a lush texture of sound by each musician being subservient to the music. Not everyone can be the famed violin soloist that gets all the props. However, that soloist will sound empty without the other parts. Some players don't want to be that support. Some relish it.
The main reason I switched to flatwounds is that my perception of my role and what I feel needs to be heard has changed. Since diving into my "role" as a supporting player in the rhythm section and using flatwounds to give my bass a stronger "bass" sound, I no longer fight to be heard. I hear myself better than I ever have, not because I'm using flats now, but because my perception of my sound is clearer in my mind. I'm playing at lower volumes than ever before and yet because I have no desire to cut through, I hear every note I play better and more defined (even in a very loud, rich rock music environment) because what I'm listening to is the bass. I have no expectations that any guitar-like tones emanating from roundwound strings better be just as loud and present as the guitars' sound. I'm fortunate in that I love to hear my talented guitarists' riffs singing on top of my supporting rhythm. The single biggest change to my sound is not my flatwound strings, but my perception and expectations as to what my bass should be delivering.