Insulation of any kind will absorb a small amount of sound energy but you need additional mass and resiliency in the wall itself.

Are there other paths for the sound like windows? If so, they will need to be triple pane glass, etc.

Can you build a fence?

Sound reduction is complicated, the weakest link will always get you due to flanking.

I can't speak to roxul (which I think is very good) vs. closed cell foam specificially, but if you're already tearing out the wall I would definitely recommend looking into green glue product (below). Used it as part of our custom home theater design and have been very pleased over 10 years of very loud and massive bass use. Of course we also had multiple layers of drywall, sound damping, isolated walls and RC channel....

RC channel is also another way to significantly reduce sound and if you're tearing out the drywall I'd use that as well. Use the clips and channel,not the stuff that attaches directly to hte wall.

Here is a link that might be useful: Green Glue

Because a product is a good thermal insulator doesn't mean it's a good sound insulator. Foamboard is a perfect example of that.

Below is a table of the STC's of various wall constructions from the City of Vancouver Noise Control Manual. You will see that FG doesn't help much.

As noted above, you have to consider other factors. Green Glue and Quietrock (or equivalent) are very effective. But they aren't cheap or easy. Without soundproofing windows, any benefits will be short-circuited.