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

Question

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?

Answer 1

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.