XML File valid, but not valid with ADO.NET

[Michael Kuzminski]

I have some xml from a company that usees the same xml tag underneath
different nodes. The xml is valid, but ADO.net complains when I try to load
it into a dataset. It gives me the error:

Although this XML document is well formed, it contains structure that Data
view cannot display.
The same table (Table name) cannot be the child table in two nested
relations.

I get the same error when I try to load up the xml document into the visual
studio .net IDE and browse the data through the GUI.

Microsoft has a knowledge base article about this, but doesn't say how to
resolve it. kb article
http://support.microsoft.com/default...b;en-us;325696

Here is the sample xml file, it has two item nodes underneath different
parent nodes and I need to be able to load up this xml in a dataset for
display purposes. Does anyone have any idea how to acomplish this?

Thanks!
-----------------------------------------------------------
<?xml version="1.0" ?>
<OrdersInventoryData>
<Order>
<Item>
<id>1</id>
<qty>5</qty>
</Item>
</Order>
<Inventory>
<Item>
<id>1</id>
<qty>100</qty>
</Item>
</Inventory>
</OrdersInventoryData>
-----------------------------------------------------------

[Derek Harmon]

"Michael Kuzminski" <Mi**************@discussions.microsoft.com> wrote in message
news:B1**********************************@microsof t.com...

The xml is valid, but ADO.net complains when I try to load
it into a dataset. It gives me the error:
The same table (Table name) cannot be the child table in two nested
relations.
The error is correct because the XML isn't structured in a manner
allowing ADO.NET to infer how it should be represented within
a DataSet.
Microsoft has a knowledge base article about this, but doesn't say how to
resolve it. kb article
http://support.microsoft.com/default...b;en-us;325696
One solution would take this structure to provide foreign keys relating
Order and Inventory each to an ItemDetail, like this (omitted are any
ADO.NET's metadata attributes, essentially create the non-nested
relationships manually),

- - - normal.xml
<?xml version="1.0" ?>
<OrdersInventoryData>
<Order>
<ItemDetailNo>1</ItemDetailNo>
</Order>
<Inventory>
<ItemDetailNo>2</ItemDetailNo>
</Inventory>
<ItemDetail>
<detailno>1</detailno>
<id>1</id>
<qty>5</qty>
</ItemDetail>
<ItemDetail>
<detailno>2</detailno>
<id>1</id>
<qty>100</qty>
</ItemDetail>
</OrdersInventoryData>
- - -
I need to be able to load up this xml in a dataset for
display purposes. Does anyone have any idea how to acomplish this?

All solutions involve adapting your XML into something DataSet
finds palatable, whether manually, by XSLT, by XmlReader filter,
or in some other way.

Use XmlDocument if you need to import well-formed, but not
necessarily relationally-structured XML. If necessary, implement
an IBindingList wrapper around your XmlDocument so you can
data-bind to it.

[Bruce Wood]

I have an idea, based on the assumption that the <Item> under <Order>
and the <Item> under <Inventory> have different structure and so
cannot, ultimately, be shoehorned into the same ADO.NET database table.
If this is not so then I recommend Derek's solution.

Use XSLT (either outside your application as a pre-processing step, or
within .NET as a call) to change the names of the nodes from <Item> to
<OrderItem> and <InventoryItem>, respectively, then pull the resulting
XML document into ADO.NET. ADO.NET should then happily create two
separate tables, one called OrderItem and one called InventoryItem.

You can do this easily from within your application using the
XslTransform class's Load() and Transform() methods. You should then be
able to pass the resulting XmlReader to a DataSet's Load() method to
load the altered XML into ADO.NET.