It depends on the context, but I'd think the most common meaning would just be "wanted to (do something)." To me, "그 사과가 먹고 싶었어요" means "I wanted to eat the apple." If I meant, "I should have eaten the apple" I'd say "그 사과를 먹었어야 해요." Note that '싶다' in this sense is only used when the subject is 'I' or in a question to "you." When it's a third person wanting something, use '싶어하다' instead. Also note that the thing that is wanted typically takes a subject marker in Korean (싶다 is like an adjective). This is the same as Japanese (e.g., りんごが 食べたい = I want to eat an apple). An object marker is also possible with 싶다 but is uncommon and would result in a very marked expression. With 싶어하다, you would use an object marker like a normal verb.

well but this may be somewhat more correct. a) wanted to (do something) = (하고) 싶었다. 싶었어요. note that -었 is the past tense morpheme, and -요 is for honorific form used when meeting older people. So basically when meeting the older interlocutor, and wanna say that you wanted to eat apple in Korean, "사과를 먹고 싶었어요." b) regret something you've not done and want to express this emotion = 싶었다. 싶었어요. the same expression, but slightly different shade of meaning. It is commonly used in an act of speaking his/her thoughts aloud when regretted. i. e."정말 착하게 살고 싶었어요... (하지만 착하게 살지 못했어요)" = I should have led a good life... in short, a) wanting, b) regretting. As Joshua said, it desperately depens on the situation and context.

well, it is almost the same as "should have p.p" clause in English. i. e. 그 사과가 먹고 싶었어요. = I should have eaten the(or that) apple.