[ACCEPTED]-XML query() works, value() requires singleton found xdt:untypedAtomic-xquery

Accepted answer
Score: 71

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 2 XPath in ( ... ) and add a [1] to simply select the 1 first value of that sequence.

Score: 8

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

0

Score: 3

For those interested in performance I ran 7 a query to compare these approaches and 6 the first option with "() and add a [1]" 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 ()[1] is 2 over 5 times faster! Execution plan is 1 much different.

More Related questions