Convert Dataset with Tables with Relations to Flat view

1 Apr 2006 3:50 PM

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>

1 Apr 2006 4:26 PM

Vijay

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

Vijay

Show quote

"Joe" <hchs1***@yahoo.com> wrote in message
news:u2qM1PaVGHA.4608@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>
>

1 Apr 2006 5:07 PM

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.

--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

Show quote

"Joe" <hchs1***@yahoo.com> wrote in message
news:u2qM1PaVGHA.4608@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>
>

1 Apr 2006 5:13 PM

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

Show quote

"Joe" <hchs1***@yahoo.com> schreef in bericht
news:u2qM1PaVGHA.4608@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>
>

3 Apr 2006 10:56 AM

Otis Mukinfus

Show quote

On Sat, 1 Apr 2006 10:50:08 -0500, "Joe" <hchs1***@yahoo.com> wrote:

>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