Hi Bajju,
Still I do not have time to give answer in c#, but I have one solution, you can do it with Sqlserver side too,
Just create procedure as below with 2 parameter : 1 input xml 2. search string
The above procedure will return single or multiple row based on search string.
'GetSpecificXMLRow' PROCEDURE --Create procedure GetSpecificXMLRow
--(
-- @str nvarchar(max) ,
-- @searchString (500)
--)
--As
--Begin
--remove this bleow declaration when procedure creation
Declare @str nvarchar(max) = '
<Highlight>
<highlightsreport>
<s_no>504</s_no>
<Highlightstext>AAAAAAAA</Highlightstext>
<regionname>XYZ</regionname>
</highlightsreport>
<highlightsreport>
<s_no>557</s_no>
<Highlightstext>BBBB</Highlightstext>
<regionname>MMT</regionname>
</highlightsreport>
<highlightsreport>
<s_no>508</s_no>
<Highlightstext>KKKKK</Highlightstext>
<regionname>ARUNACHAL PRADESH</regionname>
</highlightsreport>
</Highlight>'
,@searchString varchar(500) = 'MMT'
DECLARE @idoc INT
EXEC sp_xml_PrepareDocument @idoc OUTPUT, @str
SELECT
s_no ,
Highlightstext ,
regionname
INTO #tempReferenceNoList
FROM OPENXML(@idoc,'/Highlight/highlightsreport', 2)
WITH(
s_no VARCHAR(MAX),
Highlightstext varchar(max),
regionname VARCHAR(100)
)
select * from #tempReferenceNoList --remove this table when procedure creation else return 2 table
select * from #tempReferenceNoList where regionname like @searchString+'%'
drop table #tempReferenceNoList
--End
Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator