Many-To-Many DataSet as XML document
-
Let's say I have 3 tables which are Users, Groups and UserGroup, where a user can be a member of multiple groups and a group can obviously contain multiple users. The UserGroup table is simply a 2 column table that maintains the many-to-many relationship within the database, and this all works perfectly.
--------- ------------- ---------- | Users | 1 ----- many | UserGroup | many ----- 1 | Groups | --------- ------------- ----------
When I attempt to load these three tables into an XML document (via a dataset) it fails with an error saying "A child row has multiple parents" - presumably because the link table (UserGroup) contains duplicate UserID's and GroupID's (Which it should!). I've tried adding an artificial primary key to the UserGroup table, but it still doesn't work. The current code is below, and a sample of the output that I would like is also provided. What do I need to do to get this to work correctly? I've been Googling all morning, but I can't seem to find anything with an actual answer!// sproc returns a dataset containing three tables SqlDataAdapter da = new SqlDataAdapter( "sproc", connection ); da.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet ds = new DataSet(); da.Fill(ds); ds.Tables[0].TableName = "User"; ds.Tables[1].TableName = "Group"; ds.Tables[2].TableName = "UserGroup"; DataRelation ea1 = ds.Relations.Add( ds.Tables["User"].Columns["UserID"], ds.Tables["UserGroup"].Columns["UserID"], false); DataRelation ea2 = ds.Relations.Add( ds.Tables["Group"].Columns["GroupID"], ds.Tables["UserGroup"].Columns["GroupID"], false); ea1.Nested = true; //ea2.Nested = true; // Do I need this? XmlDataDocument doc = new XmlDataDocument(ds);I'm looking for something like this:<Groups> <Group> <GroupName>Astronauts</GroupName> <Users> <Name>Neil Armstrong</Name> <Name>Buzz Aldrin</Name> <Name>Yuri Gagarin</Name> <Users> </Group> <Group> <GroupName>Americans</GroupName> <Users> <Name>Neil Armstrong</Name> <Name>Buzz Aldrin</Name> </Users> </Group> </Groups>Does anyone know how to do this (or something very similar)? It would be greatly appreciated! Thank you.<