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

sql, sql-server, sql-server-2005, t-sql, xquery

Solution

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.

Problem

I have a typed xml document stored as text. So I use CONVERT the data type to xml by using a Common Table Expression in order to be able to use XML methods: ``` WITH xoutput AS ( SELECT CONVERT(xml, t.requestpayload) 'requestpayload' FROM TABLE t WHERE t.methodid = 1) SELECT x.requestpayload.query('declare namespace s="http://blah.ca/api";/s:validate-student-request/s:student-id') as studentid FROM xoutput x ``` Query works, returning to me the element. But I'm only interested in the value: ``` WITH xoutput AS ( SELECT CONVERT(xml, t.requestpayload) 'requestpayload' FROM TABLE t WHERE t.methodid = 1) SELECT x.requestpayload.value('declare namespace s="http://blah.ca/api";/s:validate-student-request/s:student-id', 'int') as studentid FROM xoutput x ``` This gives me the following error: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *' What I've googled says that the XPATH/XQUERY needs to be inside parenthesis and/or needs "[1]" - neither has worked. There's only one student-id element in the xml, though I guess the schema allows for more? Additionally, there are numerous element values I'd like to retrieve - is there a way to declare the namespace once rather than per method call?

Original source