By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,216 Members | 1,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,216 IT Pros & Developers. It's quick & easy.

Convert Dataset with Tables with Relations to Flat view

P: n/a
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>
Apr 1 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I believe using the relations to flat will be easy and efficient method to
do it.

Vijay

"Joe" <hc******@yahoo.com> wrote in message
news:u2**************@tk2msftngp13.phx.gbl...
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>

Apr 1 '06 #2

P: n/a
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Joe" <hc******@yahoo.com> wrote in message
news:u2**************@tk2msftngp13.phx.gbl...
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>

Apr 1 '06 #3

P: n/a
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?...3-8a9529b2b38b

I hope this helps,

Cor
"Joe" <hc******@yahoo.com> schreef in bericht
news:u2**************@tk2msftngp13.phx.gbl...
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>

Apr 1 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.