SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 how to avoid duplicate data????
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql4us
Starting Member

24 Posts

Posted - 12/09/2011 :  12:54:57  Show Profile  Reply with Quote
Unable to achieve the expected results as shown below..still get duplicates please any help would be appreciated...using string builder

Asp.net code:

protected void Page_Load(object sender, EventArgs e)
{

sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
sb.Append("<DATA>");
getdata();
sb.Append("</DATA>");
string encodexml = encodeXML(sb.ToString());
sendXMLcontent(encodexml);


}
private void getdata()
{
using (SqlConnection myConn = new SqlConnection(connStr))
{
try
{

SqlCommand myCommand = new SqlCommand("[SP]", myConn);
myCommand.CommandType = CommandType.StoredProcedure;

myConn.Open();
SqlDataReader sdr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

while (sdr.Read())
{
string node1 = (sdr["STATE"].ToString());
string node2 = (sdr["Subcat1"].ToString());
string node3 = (sdr["Subcat2"].ToString());
sb.Append("<LIST>");
sb.Append("<STATE NAME=\"" + node1 + "\" >");

sb.Append("<Subcat1 NAME=\"" + node2 + "\" >");

sb.Append("<Subcat2 NAME=\"" + node3 + "\" />");

sb.Append("</Subcat1>");
sb.Append("</STATE>");
sb.Append("</LIST>");

}

myConn.Close();
myCommand.Dispose();
sdr.Close();
sdr.Dispose();
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
Response.End();
}

}
Current Results: // HERE STATE=CA but sub-category 1 has multiple subcategory2's. I want to show reslults like I mentioned in expected results below.
- <LIST>
- <STATE NAME="CA">
- <Subcat1 NAME="CA1234A">
<Subcat2 NAME="CA123" />
</Subcat1>
</STATE>
</LIST>
- <LIST>
- <STATE NAME="CA">
- <Subcat1 NAME="CA162CCG">
<Subcat1 NAME="CA147" />
</Subcat1>
</STATE>
</LIST>
- <LIST>
- <STATE NAME="CA">
- <Subcat1 NAME="CA162CCG">
<Subcat1 NAME="CA148" />
</WING>
</STATE>
</LIST>
- <LIST>
- <STATE NAME="CA">
- <Subcat1 NAME="CA162CCG">
<Subcat2 NAME="CA149" />
</Subcat1>
</STATE>
</LIST>
- <LIST>
- <STATE NAME="CA">
- <Subcat1 NAME="CA162CCG">
<Subcat2 NAME="CA216" />
</Subcat1>
</STATE>
</LIST>
Expected Results:
<LIST>
<STATE NAME="CA">
<Subcat1 NAME="CA1234A">
<Subcat2 NAME="CA123"/>
</Subcat1>
<Subcat1 NAME="CA162CCG">
<Subcat2 NAME="CA146"/>
<Subcat2 NAME="CA147"/>
<Subcat2 NAME="CA148"/>
<Subcat2 NAME="CA216"/>
</Subcat1>
</STATE>
</LIST>
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000