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

Remy Lebeau · Accepted Answer · 2009-08-14 21:29:54Z

2

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)')

answered Aug 14, 2009 at 21:29

Remy Lebeau

610k36 gold badges516 silver badges875 bronze badges

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

1 Answer 1