The BSOD *does* happen in NT/XP, but the default option is to automatically reboot and write the message to a logfile on the hard disk instead of displaying it. You can adjust its behavior in Control Panels/System/Advanced/Startup And Recovery -- if you tell it to not reboot automatically, you will get the BSOD as usual.
That said, it happens a lot less than it does with the Win95-derived kernels, at least in my experience.
As for exactly what causes it -- it's some sort of error in the kernel, which could be caused by a variety of things. Common hardware causes are an overclocked memory/CPU corrupting data, or causing a CPU register to mis-latch (same basic effect). Failing hard drives are another frequent culprit. NT/XP is much more resistant, however, to badly behaving programs (except for low-level drivers and the like), and will not usually die if a program tries to overwrite system memory or does bad things to operating system resources.