yes and noI would say it would be the same as a controlled sinking for an artificial reef, which includes the removal of anything that would be toxic to sea life.
I don't think the SM-6 was made to sink a ship the size of the Ruben James as it is first and foremost a SAM. More for use against OCVs,small boats or causing damage to an enemy ship of that size. Although 3 or 4 SM-6 may not sink a 4000t warship they will cripple it's ability to operate. A Harpoon or two would be a Captains first choice.
the real flexibility is that without changing the seeker, they can insert modified code and its profile changes.
an SM6 at TV is going to very much hurt any vessel, and in the right spot it will be terminal in more ways than one.....