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="u rn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSe t" msdata:IsDataSe t="true"
msdata:UseCurre ntLocale="true" >
<xs:complexType >
<xs:choice minOccurs="0" maxOccurs="unbo unded">
<xs:element name="vfffg">
<xs:complexType >
<xs:sequence>
<xs:element name="ID" type="xs:string " minOccurs="0" />
<xs:element name="Sum_x0020 _of_x0020_CarCo st" 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_CarCo st" 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_CarCo st" type="xs:double "
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constrain t1">
<xs:selector xpath=".//vfffg" />
<xs:field xpath="ID" />
</xs:unique>
<xs:unique name="e1_Constr aint1" msdata:Constrai ntName="Constra int1">
<xs:selector xpath=".//e1" />
<xs:field xpath="ID" />
<xs:field xpath="CarYear" />
</xs:unique>
<xs:keyref name="e1" refer="e1_Const raint1">
<xs:selector xpath=".//e2" />
<xs:field xpath="ID" />
<xs:field xpath="CarYear" />
</xs:keyref>
<xs:keyref name="e0" refer="Constrai nt1">
<xs:selector xpath=".//e1" />
<xs:field xpath="ID" />
</xs:keyref>
</xs:element>
</xs:schema>

Apr 1 '06 #1

Subscribe Reply

2870

Vijay

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******** ******@tk2msftn gp13.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="u rn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSe t" msdata:IsDataSe t="true"
msdata:UseCurre ntLocale="true" >
<xs:complexType >
<xs:choice minOccurs="0" maxOccurs="unbo unded">
<xs:element name="vfffg">
<xs:complexType >
<xs:sequence>
<xs:element name="ID" type="xs:string " minOccurs="0" />
<xs:element name="Sum_x0020 _of_x0020_CarCo st"
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_CarCo st"
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_CarCo st"
type="xs:double " minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constrain t1">
<xs:selector xpath=".//vfffg" />
<xs:field xpath="ID" />
</xs:unique>
<xs:unique name="e1_Constr aint1" msdata:Constrai ntName="Constra int1">
<xs:selector xpath=".//e1" />
<xs:field xpath="ID" />
<xs:field xpath="CarYear" />
</xs:unique>
<xs:keyref name="e1" refer="e1_Const raint1">
<xs:selector xpath=".//e2" />
<xs:field xpath="ID" />
<xs:field xpath="CarYear" />
</xs:keyref>
<xs:keyref name="e0" refer="Constrai nt1">
<xs:selector xpath=".//e1" />
<xs:field xpath="ID" />
</xs:keyref>
</xs:element>
</xs:schema>

Apr 1 '06 #2

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]
- mv*@spam.guard. caspershouse.co m

"Joe" <hc******@yahoo .com> wrote in message
news:u2******** ******@tk2msftn gp13.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="u rn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSe t" msdata:IsDataSe t="true"
msdata:UseCurre ntLocale="true" >
<xs:complexType >
<xs:choice minOccurs="0" maxOccurs="unbo unded">
<xs:element name="vfffg">
<xs:complexType >
<xs:sequence>
<xs:element name="ID" type="xs:string " minOccurs="0" />
<xs:element name="Sum_x0020 _of_x0020_CarCo st"
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_CarCo st"
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_CarCo st"
type="xs:double " minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constrain t1">
<xs:selector xpath=".//vfffg" />
<xs:field xpath="ID" />
</xs:unique>
<xs:unique name="e1_Constr aint1" msdata:Constrai ntName="Constra int1">
<xs:selector xpath=".//e1" />
<xs:field xpath="ID" />
<xs:field xpath="CarYear" />
</xs:unique>
<xs:keyref name="e1" refer="e1_Const raint1">
<xs:selector xpath=".//e2" />
<xs:field xpath="ID" />
<xs:field xpath="CarYear" />
</xs:keyref>
<xs:keyref name="e0" refer="Constrai nt1">
<xs:selector xpath=".//e1" />
<xs:field xpath="ID" />
</xs:keyref>
</xs:element>
</xs:schema>

Apr 1 '06 #3

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?...3-8a9529b2b38b

I hope this helps,

Cor
"Joe" <hc******@yahoo .com> schreef in bericht
news:u2******** ******@tk2msftn gp13.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="u rn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSe t" msdata:IsDataSe t="true"
msdata:UseCurre ntLocale="true" >
<xs:complexType >
<xs:choice minOccurs="0" maxOccurs="unbo unded">
<xs:element name="vfffg">
<xs:complexType >
<xs:sequence>
<xs:element name="ID" type="xs:string " minOccurs="0" />
<xs:element name="Sum_x0020 _of_x0020_CarCo st"
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_CarCo st"
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_CarCo st"
type="xs:double " minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constrain t1">
<xs:selector xpath=".//vfffg" />
<xs:field xpath="ID" />
</xs:unique>
<xs:unique name="e1_Constr aint1" msdata:Constrai ntName="Constra int1">
<xs:selector xpath=".//e1" />
<xs:field xpath="ID" />
<xs:field xpath="CarYear" />
</xs:unique>
<xs:keyref name="e1" refer="e1_Const raint1">
<xs:selector xpath=".//e2" />
<xs:field xpath="ID" />
<xs:field xpath="CarYear" />
</xs:keyref>
<xs:keyref name="e0" refer="Constrai nt1">
<xs:selector xpath=".//e1" />
<xs:field xpath="ID" />
</xs:keyref>
</xs:element>
</xs:schema>

Apr 1 '06 #4

Similar topics

2705

Error creating a dataset

by: Jade | last post by:

Hi, I am trying to create a dataset with 6 tables and 5 relationships created between them. I am creating it in the Form_Load event. However i keep getting the following error... An unhandled exception of type 'System.ArgumentNullException' occurred in system.data.dll Additional information: 'column' argument cannot be null. The code stops on this line... Dim rel_Booking_CaraBook As New DataRelation("dsFullBooking",...

.NET Framework

10038

sorting a dataset

by: suzy | last post by:

hello. how can i sort data in a dataset? all the examples i have seen on msdn, etc are sorting a dataview. this works fine, but i want to return the results in xml and the dataview doesn't have a .getxml method (unlike the dataset). any ideas? thanks.

.NET Framework

5341

Saving Dataview to a Dataset

by: Dennis | last post by:

Hi, I am hoping I can get some help with a small problem I have run into using C#. U have an XML file that I load into a Dataset and then display this in a Datagrid. No problems doing this at all. I then use a Dataview to filter this view using a keyword and I have no problems with this either. What I would like to do is to save this Dataview to a new Dataset so I can save the reslts to a new file but here is where I am having the...

C# / C Sharp

1447

XmlDataDocument from DataSet with Nested DataRelations

by: George Durzi | last post by:

I have a DataSet with 3 tables, and two DataRelations dsSubs.Tables.TableName = "Subscriptions" dsSubs.Tables.TableName = "AccountManagers" dsSubs.Relations.Add "AccountManagers_Subscriptions", dsSubs.Tables.Columns dsSubs.Tables.Columns) dsSubs.Tables.TableName = "NewsFeeds"

ASP.NET

1522

retrieve a record from a dataset?????

by: DaveF | last post by:

How can I retrieve display_names Dim custDS As DataSet = New DataSet Dim mySQL, mySQL2, mySQL3 As String

ASP.NET

2022

error on query of derived datatable in dataset

by: cindy | last post by:

Get data into datatable, add to dataset dsSearch " Get data into datatable, add to dataset dsSearch Using In-Memory SQL Engine join the tables and select the filenames from the join, add to dataset dsSearch CODE ON http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=3994&lngWId=10 using SQL connection get data from view on sql server, add to dataset dsSearch

ASP.NET

1476

Help constructing a database-type view from a DataSet object

by: Gary | last post by:

I have a DataSet where two tables have a relation (the parent / child relationship is also successfully added to the DataSet Relations collection). I am looking for a way to programmatically construct something analogous to a database view where the record (or row) would show table1.Field1, table1.field2, table2.Field2, etc. I tried using a DataView object, but this appears to allow only filtering and sorting.

Visual Basic .NET

2489

Q: Adding a column to a dataset

by: Geoff Jones | last post by:

Hi Can anybody help me with the following, hopefully simple, question? I have a table which I've connected to a dataset. I wish to add a new column to the beginning of the table and to fill it with incremental values e.g. if the tables looks like this: 23 56

Visual Basic .NET

2871

Walk thru all relations in dataset

by: Joe | last post by:

Hi I have a dataset with 3 tables and 2 relations Is there a way to when I am at 1 row to tell if there is a relation on that row ??? I have the code hardcoded but try to make it work if the # of tables and #relations increase or decrease So I can just pass any dataset and walk thru the rows?? Thanks

C# / C Sharp

9592

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10230

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10058

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9870

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7416

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6678

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5450

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3972

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3576

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP