I'm an "old school" (child) psych CNS, and have spent much of my advanced practice career as a psychotherapist (that is what graduate education in psych nursing used to consist of, intensive training in psychotherapy). However, as near as I can tell from the limited research I've done, nursing graduate programs in psych nursing now focus heavily on Rx'ing meds and have very little content in psychotherapy -- in the case of both CNS and NP programs, now that the line between the CNS and NP roles has become so blurred and muddied in so many states.
This is not a good time to be interested in becoming a psych CNS. The ANCC is in the process of eliminating the psych CNS certifications in favor of psych NP certification, which means that schools will just quit offering psych CNS programs entirely very soon, so your only option will be psych NP. In my limited experience, the psych NPs I have encountered were focused almost entirely on doing med management, the same as most psychiatrists these days, since that is much more cost effective (from the employer's perspective) than doing therapy (therapists get paid a lot less than people who can write Rxs). Also in my limited experience, in the states in which CNSs can get Rx authority, no one is willing to hire you to be a therapist, for the same reason -- employers want you to get Rx authority and write Rxs all day.
Of course, in a private practice, psych NPs are free to do as much therapy in their practice as they like. But, then, you run into the issue of not having gotten much training in therapy in school, so how good a therapist are you prepared to be?
If you really want to be a psychotherapist, there is always the option of leaving nursing and pursuing one of the disciplines that still values psychotherapy (nursing clearly doesn't).
Best wishes for your journey!