SHORT VERSION: I played the basic ("vanilla") Oblivion through completely, covering just about every faction and side quest. I found KOTN (especially in a $10 jewel case) to be a worthwhile expansion, more fun than the main story quest of Oblivion. Buy it!
LONGER VERSION:
It's hard to get the DLC expansions, and some of the better mods on the net require them. This $10 edition is a great way to get them, without Steam, Direct2Drive, or any other DRM-ish involvement. You own them on the disc, forever.
The KOTN questline, however, is the real champ here. While the main game's quest has you basically playing herald and sword-for-hire to Martin Septim, who eventually vanquishes the bad guy, KOTN makes you the proverbial 'man.'
The quest has you discovering that an immortal ancient bad guy with a Sauron-type feel is back, and sends you questing to find the relics (i.e. armor and weapons) of the Divine Crusader, to equip yourself to go toe-to-toe against him.
Along the way, you will recruit brave Knights of Nine to go with you (far superior in function and feel to the cheesy mages guild cronies you could get to accompany you previously). They assist with this quest, and are even available afterward.
This story reminded me of Indiana Jones at time, with its questing for old relics to fight a present threat. That was a good feeling, by the way.
You get a climactic battle with the bad guy (WAY more climactic that the main game's end battle) and you get to keep the Knights as your buddies afterward. They have more personality and look cooler than any of the companions you can find in the official game (absent more mods).
To summarize, I like the KOTN quest more than I liked the Oblivion Quest or Shivering Isles quest. If Oblivion is a "Game of the Year" superstar, why wouldn't you want to own the best part (and more) for a measly ten bucks?