title: XML query() works, value() requires singleton found xdt:untypedAtomic
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Query works, returning to me the element.  But I'm only interested in the value:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This gives me the following error:</p>
  
  <blockquote>
    <p>'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'</p>
  </blockquote>
  
  <p>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?</p>
  
  <p>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?</p>