[ACCEPTED]-XML query() works, value() requires singleton found xdt:untypedAtomic-xquery
You need to use this:
SELECT x.requestpayload.value('declare namespace s="http://blah.ca/api"; (/s:validate-student-request/s:student-id)', 'int') AS studentid FROM xoutput x
You need to put your 2 XPath in
( ... ) and add a
 to simply select the 1 first value of that sequence.
I believe this might also do:
SELECT x.requestpayload.query('declare namespace s="http://blah.ca/api"; /s:validate-student-request/s:student-id').value('.', 'int') as studentid FROM xoutput x
For those interested in performance I ran 7 a query to compare these approaches and 6 the first option with "() and add a " was 5 MUCH faster than ".query('strFranchise').value('.',...)".
Difference 4 in Execution plan was 15% to 85% when running 3 one after the other on same data. So () is 2 over 5 times faster! Execution plan is 1 much different.
More Related questions