URGENT: Multidimensiona l hierarchical XML in TreeView

[Björn Bengtsson]

Hello!

I have an urgent problem concerning ASP.NET, ADO.NET, SQL Server, XML and
the TreeView control.
I have two tables; one describing the products and one describing their
relationships. A product may consist of multiple other products. So far
everything is great. But one product may exist as a subproduct to multiple
parent products.

My tables:
Products(prod_i d, nam)
ProductRelation (prod_id, parent_id)

My relation:
Product 1 consists of Product 2 and Product 3.
Product 4 consists of Product 5 AND Product 3.

My data in the table ProductRelation :
product 2, product 1
product 3, product 1
product 5, product 4
product 3, product 4 // This is what causes the error; product 3.

My intentions:
Retrieving the data from a MS SQL Server 7 and transforming it into XML, a
TreeView control is to be used to display the hierarchical data. The problem
is that I don't know how to make this work. Storing it and retrieving it is
of no problem, but the fact that product 3 is nested in two different places
makes it a bit hard.
Think of it as a manufacturer who makes different products as tables and
chairs and nails. Nails are used in both tables and chairs...

My code:
//DataSet
DataSet ds = new DataSet("COMPON ENTDS");

//Fill DataSet
da.Fill(ds, "COMPONENT" );

// Create a recursive data relation.
DataColumn col1 = ds.Tables[0].Columns["prod_id"];
DataColumn col2 = ds.Tables[0].Columns["parent_id"];
DataRelation drel = new DataRelation("P RODUCTS_RECURSI VE", col1, col2,
true);
drel.Nested = true;
ds.Relations.Ad d(drel);

//XmlDocument to hold XML generated from DataSet
XmlDocument objDoc = new XmlDocument();

//Load XML
objDoc.LoadXml( ds.GetXml());

//Create XslTransform object
XslTransform objXSL = new XslTransform();

//Load XSLT stylesheet
objXSL.Load(Ser ver.MapPath("hi erarchy.xslt")) ;

//StringWriter to temporarily hold result of the transformation
StringWriter writer = new StringWriter();

//Apply transformation with no arguments and dump results to StringWriter.
objXSL.Transfor m(objDoc.Create Navigator(),nul l,writer);

//Set TreeView's TreeNodeSrc property to get XML from StringWriter.
// tvTest2 is the TreeView control.
tvTest2.TreeNod eSrc = writer.ToString ();

//Bind TreeView
tvTest2.DataBin d();

//Close StringWriter
writer.Close();
Some notes:
This code works fine if I don't insist on having a product as a sub product
to multiple products. The drel.Nested = true makes it so easy. (: However,
this won't work like I want it. I've tried setting ds.EnforceConst raints =
false, but that only throws an error when trying the ds.GetXml().
I found the methodology at this URL:
http://aspalliance.com/joteke/treevi...e/article.aspx

Can anyone help me with this problem?
How do I EASILY get this kind of multidimensiona l parent-child relationship
converted to hierarchical XML so that the TreeView control can display it?

- Björn