Xquery syntax error in SQL server 2012

Question

I want to return the character 'o' in my sql database while working with xml data so I wrote the following query:

use master 
select song_type.query ('table/[where o.name like % o %]')
from xmldata

but the program returned an error saying:

Msg 9341, Level 16, State 1, Line 2
XQuery [xmldata.song_type.query()]: Syntax error near '[', expected a step expression.

please how do I fix this.

Answer 1

Try this ...

select * 
from xmldata where cast(convert(varchar(max),song_type) as xml).value("o.name[0]","varchar(500)") like '% o %'