XCIs are basically raw dumps of game cartridges incl. empty areas (like ISOs) and NSPs are all the meaty software bits in a package - similar to a Zip file but without compression. (NSZs are the compressed version.)
XCIs were great with SX OS where you could use them to emulate a cartridge - without having to install them. Nowadays, there’s not much difference in the UX. Both formats need to be installed to the console in order to be able to play them.