XML from SQL column: Cannot call methods on nvarchar(max)

Question

I have a sql query that is kicking back with an error on my column name saying 'cannot call methods on nvarchar(max).

      SELECT [LEARNER_COURSE_XML_TEST].[XML_EX].Query('declare namespace
      x="http://tempuri.org/cmi.xsd";] (/x:cmi/x:core/x:time_taken)') 
      AS TimeTaken FROM [LEARNER_COURSE_XML_TEST]

The issue seems to centre around [XML_EX].value but I've tried a few things including changing the column type but i've finally come unstuck. Any pointers would be greatly appreciated.

Answer 1

Sounds like XML_EX is of type nvarchar(max). Try changing it to xml.

You can also cast it in the query, like so:

select  cast(lcxt.XML_EX as xml).query(...)
from    learner_course_xml_test lcxt

Answer 2

Thanks for your responses guys. Turns out I was over complicating it as I don't have access to my namespace in the SQL table. I did however start by changing my field type to XML so thanks Andomar. My solution is below:

SELECT [LEARNER_COURSE_XML_TEST].[XML_EX].query('data(sco/cmicore/total_time)') AS  TimeTaken FROM [LEARNER_COURSE_XML_TEST] 

This extracts my total times as i'd hoped. Thanks again.