Inverting the DataRelation nesting order for XmlDataDocument?

[Roger W.]

Hi,

I'm trying to use the XmlDataDocument to allow Xpath queries of relational
data I've loaded from a SQL database (in VC#2005 / SQL2005). I have loaded a
DataSet with the proper tables, created their relationships, and set them to
Nested=true. But the resulting XDD is "inside out" from what I'm looking
for.

To use a standard bookstore analogy, I want a node in the Orders table to
list the ItemID and then the corresponding ItemName. But since the (ItemID,
ItemName) table is the parent table (primary key), not the child, I wind up
with a list of orderIDs that include the ItemID and not the other way
around.

I know that my way would create a duplication of text data in the XML,
essentially detailing the ItemName every time it's ordered, but that's ok
with me, if it's a legal operation. Then I could query for: "all orders that
include book "SomeBook", rather than looking up the ID for that book then
querying for orders containing that ID. I also want to do this in a way that
preserves the data relationship so that I can edit the XmlNode and Update
the changes back into the database. Is this possible?

Example of what I'd like to see:

Order 1
ItemId 1000
ItemName "SomeBook"
ItemId 1001
ItemName "OtherBook"
Order 2
ItemId 1001
ItemName "OtherBook"

Thanks!

-Roger

 

[Sahil Malik [MVP C#]]

A bit hard to comment on without seeing your current data structure
visually, just as you have represented your wanted structure visually.

One thing you could try is reverse the datarelation, - this obviously
depends on what is "one" and what is "many" in your one to many
relationships (which is why I need to see the actualy data)
Otherwise XSLT will work.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________