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?
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.