Closed cell foam only in your zone. See this article: https://www.buildingscience.com/documents/guides-and-manuals/gm-2102-residential-spray-foam-guide

Note that it says: "In IECC Climate Zones 5 and higher only high density closed cell spray foam should be used."

It is permissible to construct a sealed, conditioned attic using a combination of closed and open cell foam insulation installed in layers. It is also permissible to use a combination of closed cell and non-spray foam insulation (e.g., cellulose or fiberglass.) It's imperative that you spray enough closed cell under the roof decking so that it's vapor impermeable or you risk rotting the roof deck.

I, thankfully, don't live in zone 6, but if I did, I would construct a conventional vented attic with good air sealing from the living space. To this end, I'd spray closed cell foam on the attic side of the living space ceiling and then supplement it with cellulose insulation to achieve the required R-value.

I am no expert and will defer to Charles Ross on building science. But I do know a poorly ventilated attic will have moisture problems. Here is an interesting slide deck I found discussion open cell foam on roof decks in cold climates:

Why Are Attics Insulated With Open-Cell Spray Foam So Damp?