[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

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.

Source: stackoverflow.com

How to insert xml into a node in another xml using XQuery?

SQL Server SELECT to JSON function

Use of xml.modify to insert parameters into specific element of an xml column

Getting multiple records from xml column with value() in SQL Server

IF/ELSE Stored Procedure

Conditional Operator in SQL Where Clause

SQL Server 2005 Get First and Last date for any Month in any Year

How to create sequential number column index on table with data

retrieve the most recent record for each customer

Why can't I perform an aggregate function on an expression containing an aggregate but I can do so by creating a new select statement around it?

Cookie Warning

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.