What is the best SQL Server field type to cast to when storing a UNC path stored in a sql_variant field?
If the path is being stored in a SQL_VARIANT field, then you might as well CAST / CONVERT to NVARCHAR(4000) on the way out as that is the most that can be stored by a SQL_VARIANT. While the typical upper limit for a path is 260 characters, it is possible to exceed that and go up to 32,767 characters (even if that is unlikely to happen in practice). Since this scenario concerns storing the value in a local variable or returning in a result set (as opposed to how it is stored in the table), there is no reason to use less than 4000, and no use in specifying MAX since the value stored in the table couldn't hold that data anyway.

However, why would a path be stored as a SQL_VARIANT in the first place? Even if the total length never exceeds even the default 260 characters, it would still be best to store as NVARCHAR(500), or NVARCHAR(1000), or something like that.