It is a question of failure mode. If your hard drive motor dies, if the heads fail, if the electronics fail, if the sealing fail (in some cases it can be repaired by specialists but that's not easy and much more expensive than a tape reader)... lots of things can go wrong that are not related with the media itself. The advantage of tape is that the device that reads it is separate from the mechanically simple tape. It comes at a cost but that's for hedging against a different risk.
LTO-9 drives are expensive, but the media is around $90 for an 18TB (pre-compression) tape. That’s a pretty good price if you make lots of backups for work. They also offer append-only mode, which is awesome for archives where you want it to be essentially impossible to delete a backup without physical access.
But the tape itself has a great reputation for being physically robust. There are fewer parts to break than in an HDD, too. If your tape drive dies, you can replace it and keep using the same media. That’d be like having hard drive platters you could swap into another HDD later on.
Tape media doesn't last forever. I suppose that tapes specifically meant for archiving are better, but I have old audio tapes from the 1970s and 80s that are just falling apart.
Mylar audio tape of that vintage had longevity problems. I remember reading about an audio archive that thought they had done the right thing by preserving onto high quality tape of the time and then they found their archives disintegrating. I think they got lucky and moved things to new media before losing everything.
This is always the risk. Longevity testing is often done at high temperatures or other artificial means but cannot exactly simulate 30-50 years of storage. If something is important, it's best to use two different media, and check them over the years.
I believe it is cost per unit density that allows magnetic tape to be more suitable for archival storage than HDD, which surpasses it in I/O speeds. Magnetic tape storage is still usually in RAID-like configurations and while tape only survives for 15-30 years, data survival by migration is typically done well before medium degradation.
but I still don't understand what supposedly makes it more reliable long-term as storage.