I only played SotN until I got the first (?false) ending where you defeat R-dude. It was enjoyable enough, but nothing mindblowing. The game didn't compel me enough to go through the hassle of getting the '2nd' castle. Which, in hindsight, might have led to my enjoying the game quite a bit more. This was my first Castlevania.
I've only played DoS and PoR, and I have to say I've enjoyed them more than (what I've played of) SotN. A large part of it has to do with the games' systems. Playing around with souls was quite fun in DoS, particularly in developing different tactics against bosses. The dual characters in PoR with their individuals strengths also led to very fun boss battles. SotN, on the other hand, mainly consisted of using your primary weapon and *one* subweapon. I found this too limiting. Now, I understand that you apparently learn spells later on, but shrug. I should probably give SotN more of a chance sometime. The XBLA version is a good excuse. That being said, I did play for maybe ?5+ hrs (which seems to be the length of a Castlevania game anyway), and I wasn't too impressed - though that may also have something to do with my expectations after people hyping it as being so awesome.