XML Searching routine

				
--**************************************
-- Name: XML Searching routine
-- Description:Search for strings within XML tags
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=161&lngWId=5--for details.--**************************************

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>')
/*
123456
<doc>
7890123456789012345678901234
<head1>A sample doc</head1>
1234567890123456789012345678901234567890
<para>This is just a sample para</para>
1234567890123456789012345678901234567890123456789012345678
<para>Now this para contains some interesting text</para>
123456789012345678901234567890123456789
<para>And this is really boring</para>
1234567890123456789012345678901234
<footer>Some footer text</footer>
1234567
</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