Yes it does work. I am not sure if there's any wiring differences, but you can buy an original Nokia USB OTG cable from Amazon for a couple of quid.
I've got my Xperia S working with a Logitech wireless keyboard (with the adapter plugged in to the phone), and had it working with a third-party PS3 controller (except it broke!).
I've also had mixed success with USB flash drives - as some can't be powered by the phone. It's very hit and miss, to be honest - and most of my flash drives are unbranded so I can't identify who made them (they're drives given out at press events, so usually branded by the manufacturer that held the event).
As for an external HDD, it should work if it's separately powered.
I'm starting to think the cable you got isn't wired correctly!
Here's one supplier of the cable that will work: http://www.amazon.co.uk/adaptor-CA-1.../dp/B004EYL1LK
Do search for other places, as I am not endorsing the supplier either way!