Convert Dataset with Tables with Relations to Flat view

[Joe]

Hi

I have a dataset with 2 tables and Relations
What is the best way to flatten the 2 files to a new table or xml or file

I can loop thru table1 and get the childrows
or
I can do an Xpath on the xml but not sure how to get relation from xsd
or
Is there another alternative???

Currently

Table1
2000 10,000
2001 12,000

Table2
2000 mustang 2,000
2000 corvet 4,000

What I want

2000 10,000 mustang 2,000
2000 12,000 corvet 4,000

Thanks

<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true"
msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="vfffg">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:string" minOccurs="0" />
<xs:element name="Sum_x0020_of_x0020_CarCost" type="xs:double"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="e1">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:string" minOccurs="0" />
<xs:element name="CarYear" type="xs:string" minOccurs="0" />
<xs:element name="Sum_x0020_of_x0020_CarCost" type="xs:double"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="e2">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:string" minOccurs="0" />
<xs:element name="CarYear" type="xs:string" minOccurs="0" />
<xs:element name="CarModel" type="xs:string" minOccurs="0" />
<xs:element name="Sum_x0020_of_x0020_CarCost" type="xs:double"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1">
<xs:selector xpath=".//vfffg" />
<xs:field xpath="ID" />
</xs:unique>
<xs:unique name="e1_Constraint1" msdata:ConstraintName="Constraint1">
<xs:selector xpath=".//e1" />
<xs:field xpath="ID" />
<xs:field xpath="CarYear" />
</xs:unique>
<xs:keyref name="e1" refer="e1_Constraint1">
<xs:selector xpath=".//e2" />
<xs:field xpath="ID" />
<xs:field xpath="CarYear" />
</xs:keyref>
<xs:keyref name="e0" refer="Constraint1">
<xs:selector xpath=".//e1" />
<xs:field xpath="ID" />
</xs:keyref>
</xs:element>
</xs:schema>

 

[Vijay]

I believe using the relations to flat will be easy and efficient method to
do it.

Vijay

 

[Nicholas Paldino [.NET/C# MVP]]

Joe,

If you are going to use XML, then why not just export the table contents
as XML? You would have to do no work than indicating where you want the XML
written to, and all the relations will be maintained correctly (which is
better than a flat table, IMO).

If you have to create a flat table, it looks like you want an inner
join. You will have to cycle through the parent table, and then call the
GetChildRows method on each row in the parent row. If there are child rows,
then you can cycle through those and write your information (using the
parent information as well as you do) appropriately.

Hope this helps.

 

[Cor Ligthert [MVP]]

Joe,

See this sample on our website, I see you uses C#, but is should be to
translate easy

http://www.vb-tips.com/default.aspx?ID=5fd5a8cf-54dc-4946-a193-8a9529b2b38b

I hope this helps,

Cor

 

[Otis Mukinfus]

Hi

I have a dataset with 2 tables and Relations
What is the best way to flatten the 2 files to a new table or xml or file

I can loop thru table1 and get the childrows
or
I can do an Xpath on the xml but not sure how to get relation from xsd
or
Is there another alternative???

Currently

Table1
2000 10,000
2001 12,000

Table2
2000 mustang 2,000
2000 corvet 4,000

What I want

2000 10,000 mustang 2,000
2000 12,000 corvet 4,000

Thanks

[snip]
Use a join at the database?

Why do it in the application?

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com