35

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?

Jens Erat
  • 37,523
  • 16
  • 80
  • 96
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502

3 Answers3

72

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.

canon
  • 40,609
  • 10
  • 73
  • 97
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
8

I believe this might also do:

SELECT 
   x.requestpayload.query('declare namespace s="http://blah.ca/api";
                           /s:validate-student-request/s:student-id').value('.', 'int') 
  as studentid
FROM xoutput x
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ken
  • 81
  • 1
  • 1
3

For those interested in performance I ran a query to compare these approaches and the first option with "() and add a [1]" was MUCH faster than ".query('strFranchise').value('.',...)".

Difference in Execution plan was 15% to 85% when running one after the other on same data. So ()[1] is over 5 times faster! Execution plan is much different.

David Coster
  • 1,755
  • 2
  • 14
  • 16