create table #xml_tbl ( tag varchar(255) null)
insert #xml_tbl values(
'<doc>
<head1>A sample doc</head1>
<para>This is just a sample para</para>
<para>Now this para contains some interesting text</para>
<para>And this is really boring</para>
<footer>Some footer text</footer>
</doc>')
declare @search_str varchar(30), @Search_Word varchar(30)
set @Search_Word = 'just'
set @search_str = '%' + @Search_Word + '%'
select patindex(@Search_Str, tag),
charindex('</para>', tag, patindex(@Search_Str, tag)),
patindex(reverse(@Search_Str), reverse(tag)),
charindex(reverse('<para>'), reverse(tag), patindex(reverse(@Search_Str), reverse(tag))),
reverse(substring(reverse(tag), patindex(reverse(@Search_Str), reverse(tag)),
charindex(reverse('<para>'), reverse(tag),
patindex(reverse(@Search_Str), reverse(tag))) -
patindex(reverse(@Search_Str), reverse(tag)) + len('<para>')))
+
substring(tag, patindex(@Search_Str, tag) + len(@Search_Word),
charindex('</para>', tag, patindex(@Search_Str, tag)) -
patindex(@Search_Str, tag) + len('</para>') - len(@Search_Word))
from #xml_tbl |