Substring of XML datatype column value in SQL server 2005

Question

I have a table in a database in SQL server 2005.one of the column is of XML datatype.the content of the columns is like

   <info>This is a sample information .Anyone can help </info>

Now i want to query the data in the table with a part of the column value as response. ie : My desired output is "This is a sample "

What should be the query ? Substring is throwing an error

Answer 1

There are two ways to do that:

1) retreive the entire text from the XML and then let the SQL get the substring from that:

SELECT SUBSTRING(tablename.columnname.value('(/info)[1]', 'varchar(max)'), 1, 17)

2) let XQuery get the substring directly from the XML text instead:

SELECT tablename.columnname.value('substring(string((/info)[1]), 1, 17)')