XML query() works, value() requires singleton found xdt:untypedAtomic

You need to use this:

SELECT 
        x.requestpayload.value('declare namespace s="http://blah.ca/api";
            (/s:validate-student-request/s:student-id)[1]', 'int') 
    AS
        studentid
    FROM 
        xoutput x

You need to put your XPath in ( ... ) and add a [1] to simply select the first value of that sequence.