error in displaying XML file

Posted by Klbaiju under XML on 11/7/2017 | Points: 10 | Views : 170 | Status : [Member] | Replies : 1
Hi

this is my xml file

<?xml version="1.0" encoding="utf-8" ?>
<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
my requirement is to display Highlightstext of regionname='MMT' in gridview

following is my code

DataSet ds1 = new DataSet();
ds1.ReadXml(Server.MapPath("~/Highlightsreport/Highlightsreport.xml"));
DataView dv = new DataView(ds1.Tables["highlightsreport/Highlightstext"]);
dv.RowFilter = "regionname='MMT'";
GridView1.DataSource = ds1;
GridView1.DataBind()
there is no error showing .but problem is it is not filtering xml file .

complete file is displaying .

I need only the Highlightstext of regionname='MMT'

how to solve this

Regards

Baiju




Responses

Posted by: Ajay2707 on: 11/16/2017 [Member] Starter | Points: 25

Up
1
Down
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

Login to post response