This should be obsolete now. I think I've solved the problem.

--------------------------------
Once subscribed, Steam will download the craft and store it here:
C:\Program Files (x86)\Steam\steamapps\workshop\content\220200\2238370148

Grab that .craft file and put it in your favorite SPH folder, then it should appear. For example:
C:\Program Files (x86)\Steam\steamapps\common\Kerbal Space Program\saves\1_10 Sandbox\Ships\SPH

I have no idea what causes the workshop content being downloaded but not showing up in game. I even re-installed the whole Steam Client but in vain.