Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  .NET Inside SQL Server (2005)  how to avoid duplicate data????

Author  Topic  sql4us Starting Member 24 Posts Posted - 2011-12-09 : 12:54:57 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>