C# ADO.NET & XML

G

Guest

Hi,
I have aproblem with nesting of tables in an schema generated dataset,

What I am trying to do is nest delivery item data in a delivery table, then
out put the data to a xml file. When I do this, what should be nested xml
elements I end up getting the opposite.

An example of the code I'm using is:
DataRow newSale = this.dataset.Tables["sale"].NewRow ();

DataRow newItem = this.dataset.Relations["sale_to_item"].ChildTable.NewRow ();
..
..Add data to the appropiate columns
..
this.dataset.Tables["sale"].Rows.Add ( newSale );

this.datset.Relations["sale_to_item"].ChildTable.Rows.Add ( newItem );

this.dataset.AcceptChanges ();

Below is a sample of the XML Schemas I'm using and the code

<?xml version="1.0" ?>
<xs:schema id="PDA_PROFSALE"
targetNamespace="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns:mstns="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="PDA_PROFSALE" msdata:IsDataSet="true"
msdata:Locale="en-AU" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="sale" msdata:CaseSensitive="false" minOccurs="1"
maxOccurs="1000000" form="qualified">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" minOccurs="0"
msdata:AutoIncrement="true" msdata:AutoIncrementSeed="0"
msdata:AutoIncrementStep="1" msdata:ReadOnly="true" />
<xs:element name="co_last_name" type="xs:string" minOccurs="0" />
<xs:element name="first_name" type="xs:string" minOccurs="0" />
<xs:element name="details_date" type="xs:string" minOccurs="0" />
<xs:element name="non_GST_amount" type="xs:string" minOccurs="0" />
<xs:element name="salesperson" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
<xs:element name="salesperson_last_name" type="xs:string"
minOccurs="0" />
<xs:element name="salesperson_first_name" type="xs:string"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="item" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
<xs:element name="item_quantity" type="xs:string" minOccurs="0" />
<xs:element name="item_size" type="xs:string" minOccurs="0" />
<xs:element name="item_type" type="xs:string" minOccurs="0" />
<xs:element name="item_charge" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="other">
<xs:complexType>
<xs:sequence>
<xs:element name="last_name" type="xs:string" minOccurs="0" />
<xs:element name="given_name" type="xs:string" minOccurs="0" />
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="PDA_PROFSALEKey1" msdata:primaryKey="true">
<xs:selector xpath=".//mstns:sale" />
<xs:field xpath="mstns:ID" />
</xs:key>
<xs:keyref name="sale_to_salesperson" refer="mstns:pDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:salesperson" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:keyref name="sale_to_item" refer="mstns:pDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:item" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:keyref name="sale_to_other" refer="mstns:pDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:blush:ther" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:key name="PDA_PROFSALEKey2">
<xs:selector xpath=".//mstns:salesperson" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
<xs:key name="PDA_PROFSALEKey3">
<xs:selector xpath=".//mstns:item" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
<xs:key name="PDA_PROFSALEKey4">
<xs:selector xpath=".//mstns:blush:ther" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
</xs:element>
</xs:schema>
 
I

Ilya Tumanov [MS]

First of all, this schema has no "delivery" table (please see below). You do
have a lot of relations, though, some of them duplicated.

For example, "item" is connected to "sale" by two relations:



Nested: 'sale_item': item('ID' ) <- sale('ID' )
Related: 'sale_to_item': item('sale_ID' ) <- sale('ID' )



Seems kind of pointless to me.



Note 'nested' and 'related' are exactly the same from functionality point of
view.

The difference is in XML serialization only. Nested relations would result
in larger XML and slower parsing.



For example, table 'Parent' has a primary key 'ID' and table 'Child' had
foreign key "ParentID', columns are mapped to attributes to save space and
improve performance.

Nested xml:



<DataSet>

<Parent ID="10>

<Child ParentID="10 />

</Parent>

</DataSet>



Related XML:



<DataSet>

<Parent ID="10 />

<Child ParentID="10 />

</DataSet>



--------------------------- DataSet ----------------------
DataSet: 'PDA_PROFSALE'
----------------------- Tables -----------------------
Table@0: 'sale'
------------------- Columns ----------------------
Element column@0: 'ID' of 'Int32' Unique Autoincrement
Element column@1: 'co_last_name' of 'String' Nullable
Element column@2: 'first_name' of 'String' Nullable
Element column@3: 'details_date' of 'String' Nullable
Element column@4: 'non_GST_amount' of 'String' Nullable
----------------- Child Relations ----------------
Nested: 'sale_salesperson': salesperson('ID' ) -> sale('ID' )
Nested: 'sale_item': item('ID' ) -> sale('ID' )
Nested: 'sale_other': other('ID' ) -> sale('ID' )
Related: 'sale_to_salesperson': salesperson('sale_ID' ) ->
sale('ID' )
Related: 'sale_to_item': item('sale_ID' ) -> sale('ID' )
Related: 'sale_to_other': other('sale_ID' ) -> sale('ID' )
----------------- Parent Relations ---------------
--------------------- Rows -----------------------
0 rows in this table
--------------------------------------------------
Table@1: 'salesperson'
------------------- Columns ----------------------
Element column@0: 'sale_ID' of 'Int32' Unique
Element column@1: 'salesperson_last_name' of 'String'
Nullable
Element column@2: 'salesperson_first_name' of 'String'
Nullable
Hidden column@3: 'ID' of 'Int32' Nullable
----------------- Child Relations ----------------
----------------- Parent Relations ---------------
Nested: 'sale_salesperson': salesperson('ID' ) <- sale('ID' )
Related: 'sale_to_salesperson': salesperson('sale_ID' ) <-
sale('ID' )
--------------------- Rows -----------------------
0 rows in this table
--------------------------------------------------
Table@2: 'item'
------------------- Columns ----------------------
Element column@0: 'sale_ID' of 'Int32' Unique
Element column@1: 'item_quantity' of 'String' Nullable
Element column@2: 'item_size' of 'String' Nullable
Element column@3: 'item_type' of 'String' Nullable
Element column@4: 'item_charge' of 'String' Nullable
Hidden column@5: 'ID' of 'Int32' Nullable
----------------- Child Relations ----------------
----------------- Parent Relations ---------------
Nested: 'sale_item': item('ID' ) <- sale('ID' )
Related: 'sale_to_item': item('sale_ID' ) <- sale('ID' )
--------------------- Rows -----------------------
0 rows in this table
--------------------------------------------------
Table@3: 'other'
------------------- Columns ----------------------
Element column@0: 'last_name' of 'String' Nullable
Element column@1: 'given_name' of 'String' Nullable
Element column@2: 'sale_ID' of 'Int32' Unique
Hidden column@3: 'ID' of 'Int32' Nullable
----------------- Child Relations ----------------
----------------- Parent Relations ---------------
Nested: 'sale_other': other('ID' ) <- sale('ID' )
Related: 'sale_to_other': other('sale_ID' ) <- sale('ID' )
--------------------- Rows -----------------------
0 rows in this table
--------------------------------------------------


Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

Aus Bean said:
Hi,
I have aproblem with nesting of tables in an schema generated dataset,

What I am trying to do is nest delivery item data in a delivery table,
then
out put the data to a xml file. When I do this, what should be nested xml
elements I end up getting the opposite.

An example of the code I'm using is:
DataRow newSale = this.dataset.Tables["sale"].NewRow ();

DataRow newItem = this.dataset.Relations["sale_to_item"].ChildTable.NewRow
();
.
.Add data to the appropiate columns
.
this.dataset.Tables["sale"].Rows.Add ( newSale );

this.datset.Relations["sale_to_item"].ChildTable.Rows.Add ( newItem );

this.dataset.AcceptChanges ();

Below is a sample of the XML Schemas I'm using and the code

<?xml version="1.0" ?>
<xs:schema id="PDA_PROFSALE"
targetNamespace="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns:mstns="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="PDA_PROFSALE" msdata:IsDataSet="true"
msdata:Locale="en-AU" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="sale" msdata:CaseSensitive="false" minOccurs="1"
maxOccurs="1000000" form="qualified">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" minOccurs="0"
msdata:AutoIncrement="true" msdata:AutoIncrementSeed="0"
msdata:AutoIncrementStep="1" msdata:ReadOnly="true" />
<xs:element name="co_last_name" type="xs:string" minOccurs="0" />
<xs:element name="first_name" type="xs:string" minOccurs="0" />
<xs:element name="details_date" type="xs:string" minOccurs="0" />
<xs:element name="non_GST_amount" type="xs:string" minOccurs="0" />
<xs:element name="salesperson" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
<xs:element name="salesperson_last_name" type="xs:string"
minOccurs="0" />
<xs:element name="salesperson_first_name" type="xs:string"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="item" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
<xs:element name="item_quantity" type="xs:string" minOccurs="0" />
<xs:element name="item_size" type="xs:string" minOccurs="0" />
<xs:element name="item_type" type="xs:string" minOccurs="0" />
<xs:element name="item_charge" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="other">
<xs:complexType>
<xs:sequence>
<xs:element name="last_name" type="xs:string" minOccurs="0" />
<xs:element name="given_name" type="xs:string" minOccurs="0" />
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="PDA_PROFSALEKey1" msdata:primaryKey="true">
<xs:selector xpath=".//mstns:sale" />
<xs:field xpath="mstns:ID" />
</xs:key>
<xs:keyref name="sale_to_salesperson" refer="mstns:pDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:salesperson" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:keyref name="sale_to_item" refer="mstns:pDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:item" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:keyref name="sale_to_other" refer="mstns:pDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:blush:ther" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:key name="PDA_PROFSALEKey2">
<xs:selector xpath=".//mstns:salesperson" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
<xs:key name="PDA_PROFSALEKey3">
<xs:selector xpath=".//mstns:item" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
<xs:key name="PDA_PROFSALEKey4">
<xs:selector xpath=".//mstns:blush:ther" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
</xs:element>
</xs:schema>
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

XML schema of 4KB takes 8 seconds to be read 3
"Specified cast is not valid" when generating dataset 1
Convert Dataset with Tables with Relations to Flat view 4
Can't get XSD to generate DataSet primary keys 1
DataSet.ReadXmlSchema() throw DataException - HELP!!! 2
LoadDataRow 3
Retrieve info from Dataset with Relations (Dataset,SQL or XML) 2
Can I change to field type in an XML schema automatically? 3

Top