I've played a lot of long winded JRPGs and visual novels like Trails, Utawarerumono, etc. Some were decent enough I finished them, others were so boring I dropped them.
The quality of FFXIV's story really varies. ARR does not have engaging characters. The pacing fluctuates and there are long stretches that are boring slogs. The presentation of the story with cutscenes and voice acting is pretty meh at first and doesn't get decent until Heavensward, but again your investment in the characters and the storylines will vary. There is also a noticeable writing shift around Stormblood. If you liked ARR and HW's storylines you might dislike the direction of FFXIV's latter storyline, and you might like that but the early stories might turn you off. The main story alone is 300+ hours long so it's a tall ask. If you want to play a game for the story, I'd probably recommend that you play other games first, before working your way down the list to FFXIV.
In the latest expansion, Endwalker, there were a LOT of tedious, repetitive speeches about hope and friendship. I started fast forwarding through the speeches towards the later half of the expansion's storyline.
There are side storylines, like the raid storylines. Their quality varies. The Warring Triad storyline is a boring exposition dump that sets up a plotline that was never followed up on and was eventually canned. The Four Lords is forgettable. Sorrow of Werlyt is actually 7/10 very good. Omega is fine. The first 2/3rds of Eden were okay but then the story goes bad in the last tier. The Void Ark storyline is forgettable. Return to Ivalice was fine. Save the Queen was good-ish. I dropped the Nier raids at the second raid and never finished them. The ARR Hildibrand storyline is good. The HW is... okay, borderline forgettable.
Most of the job storylines range from "okay" to bad. The only vaguely good ones are the Lancer 1-30, Samurai 50-60, Dark Knight 60-70, and Gunbreaker 60-70 storylines. Individually, none of the Scholar storylines were good, but the overall 30-80 story was good. I would hesitate to say that any of those job storylines I just listed were worth the time required to grind up a job to see them.
The role questlines are overall meh, just like the job questlines. The only decent one thus far has been the Endwalker melee DPS one. The only rolequestline I haven't done yet is the Endwalker ranged DPS one, but given the quality of the other questlines I doubt it will be particularly good.
Most beast tribe questlines are forgettable. There are some that have some moments of comedy and are above average, but again not really something I'd say was worth the grind just to see the story for. Same with the custom delivery questlines.
Almost all of the sidequests in this game are bad, not even on the same level of interest as your average WoW sidequest. If you want a story worth your time, don't do them. If you're going through the game for the first time, doing a few questing hubs along the way through the MSQ might help build atmosphere, but if you've already passed thus hubs then don't bother going back. They are as follows: Whitebrim Front in Central Coerthas Highlands. The Convictory in the Western Coerthas Highlands. Some of the Confederacy Quests in the Ruby Sea. Some quests in Garlemald were good but they are mixed among a lot of filler. Almost all of the sidequests in Elpis were somewhat interesting.
Eh. The MMO format of FFXIV gets in the way of FFXIV's storytelling ability. The zones are cramped and are filled with repetitive events, and don't effectively convey the setting like the levels of singleplayer JRPGs do. There are a lot of narrative disconnects, like you canonically travelling with party members but they do not appear with you outside of cutscenes, and you don't get to actually adventure with them in gameplay outside of duties, or until you reach Shadowbringers (at which point they start showing up in dungeons in the place of other players). Etc.
You cannot judge FFXIV in a vacuum. As a JRPG, other games offer more effective experiences for your time and money. As an MMO, yes it's the probably the best alternative to WoW atm but has a lot of things that get in the way of the user experience (having to go through a 300+ hour long story you are mostly doing by yourself to reach endgame).