Edit Question - KnowledgeHub

Title Body

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?

Tags (comma-separated) Cancel