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.

tim-phillips · Accepted Answer · 2014-08-21 19:14:37Z

9

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

edited Aug 21, 2014 at 19:14

tim-phillips

1,0971 gold badge13 silver badges23 bronze badges

answered Jun 8, 2012 at 10:00

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Damien_The_Unbeliever Over a year ago

CAST has to wrap the whole column reference, not just the column name (i.e. lcxt. should be inside)

Andomar Over a year ago

@Damien_The_Unbeliever: Obviously right, feel free to edit ;)

Rob Owl · Accepted Answer · 2013-11-25 10:22:33Z

1

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.

answered Nov 25, 2013 at 10:22

Rob Owl

1531 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related