Mapping dataset tables to XML output

[Henrik]

Hi,
I'm really stuck with this one. I have a dataset with two tables. One table
is company data, and the other is contacts. I populate these by using a SP in
SQL Server which returns the two tables in one call. I'm using Microsoft
EnterpriseLibra ry for Data to get the data from SP. It works fine and I get
two tables in the dataset, "Table1" and "Table2".

I then have an XML-file, which will serve as a template to generate the
XML-schema, which I load into the dataset with the InferXmlSchema method.
This gives me a perfectly good looking XML-Schema of how I want the XML-data
to be returned in the Dataset. Although, it appears that I have two schemas.
One that is the schema based on my XML-docuement, and the other which just
seem to be a plain structure of the two tables loaded into the dataset (which
really isn't the way I want to return it) with no relation to eachother.

Anyway, when I try to get the data from the two tables to an
XML-representation, they will not relate to eachother. Both tables will
simply be shown without nesting.
For example, I get this:
<company>
<name>abc</name>
</company>
<contact>
<name>123</name>
</contact>
instead of what I want and the schema I generated says:
<company>
<name>abc</name>
<contacts>
<contact>
<name>123</name>
</contact>
.... here there could be more contacts ...
</contacts>
</company>

I'm really lost here. Where am I going wrong? I have tried to rename the
tables Table1, 2 etc. to the correct names from the schema, company and
contact, but that doesn't work.

I know I can manually create relations with the dataset, but is that really
necessary? The XML-schema says it all how it should be nested. Or is it my SP
that doesn't return the info about how the two tables relate.

What is required for this to work? Can I get it to work by just using the
XML-schema generated for me, or do I have to add the relations between the
tables in code for this to work? Or should I change my SP to return the data
in another way (today it just simply are two selects from the two tables for
a specific company for example).

And, I'm also wondering another thing, which I just as well may ask. Why are
the tables in the dataset named "Table1" and "Table2" etc., instead of their
actual name as used in the SP? Is this the default behaviour that the
tablenames doesn't get returned from the SP in the two resultsets?

Well, if anyone has any hint, please let me know. But, I would like to avoid
adding relationsinform ation for the tables in the code. It really must be
possible for that to be returned automatically from the SP, or I mean, the
actual XML-schema is perfectly fine in defining how the tables should relate?
Or can't it map XML-schema to tables by the names if they are the same?

Thank you for any help!

[John Saunders]

"Henrik" <no*****@nomail x.nowrote in message
news:8B******** *************** ***********@mic rosoft.com...

Hi,
I'm really stuck with this one. I have a dataset with two tables. One
table
is company data, and the other is contacts. I populate these by using a SP
in
SQL Server which returns the two tables in one call. I'm using Microsoft
EnterpriseLibra ry for Data to get the data from SP. It works fine and I
get
two tables in the dataset, "Table1" and "Table2".

I then have an XML-file, which will serve as a template to generate the
XML-schema, which I load into the dataset with the InferXmlSchema method.
This gives me a perfectly good looking XML-Schema of how I want the
XML-data
to be returned in the Dataset. Although, it appears that I have two
schemas.
One that is the schema based on my XML-docuement, and the other which just
seem to be a plain structure of the two tables loaded into the dataset
(which
really isn't the way I want to return it) with no relation to eachother.

You can't load a schema into a dataset to determine how the XML will be
rendered. Loading a schema into a DataSet is for the purpose of configuring
tables and rows and relations.

If you need to return the XML in a different format, you're going to have to
do that on your own. Perhaps you can use an XML stylesheet.

John

[Redowl]

Henrik,

If I understand your problem correctly, what I have done to achieve similar
is create a stored procedure (sp) to return the data in XML format using the
FOR XML statement within my sp then

' Create Command object
Dim SqlCmd As New SqlCommand("p_X ML_s", SqlCon)

SqlCmd.CommandT ype = CommandType.Sto redProcedure

' Set up XmlReader
Dim XmlReader As XmlReader = SqlCmd.ExecuteX mlReader

' Get Schema File (name and path)
Dim RelationsSchema As String =
System.Web.Http Context.Current .Server.MapPath (XML-schema)

' Generate DataSet based on Schema provided
Dim MyDataset As New DataSet
MyDataset.ReadX mlSchema(Relati onsSchema)
MyDataset.ReadX ml(XmlReader, XmlReadMode.Fra gment)

' Return DataSet
Return MyDataset

This should work providiing your stored procedure is returning the data
correctly, you may need to change the XmlReadMode, I am using Fragment as my
data does not return a unique root node.
Alex

"Henrik" wrote:

Hi,
I'm really stuck with this one. I have a dataset with two tables. One table
is company data, and the other is contacts. I populate these by using a SP in
SQL Server which returns the two tables in one call. I'm using Microsoft
EnterpriseLibra ry for Data to get the data from SP. It works fine and I get
two tables in the dataset, "Table1" and "Table2".

I then have an XML-file, which will serve as a template to generate the
XML-schema, which I load into the dataset with the InferXmlSchema method.
This gives me a perfectly good looking XML-Schema of how I want the XML-data
to be returned in the Dataset. Although, it appears that I have two schemas.
One that is the schema based on my XML-docuement, and the other which just
seem to be a plain structure of the two tables loaded into the dataset (which
really isn't the way I want to return it) with no relation to eachother.

Anyway, when I try to get the data from the two tables to an
XML-representation, they will not relate to eachother. Both tables will
simply be shown without nesting.
For example, I get this:
<company>
<name>abc</name>
</company>
<contact>
<name>123</name>
</contact>
instead of what I want and the schema I generated says:
<company>
<name>abc</name>
<contacts>
<contact>
<name>123</name>
</contact>
... here there could be more contacts ...
</contacts>
</company>

I'm really lost here. Where am I going wrong? I have tried to rename the
tables Table1, 2 etc. to the correct names from the schema, company and
contact, but that doesn't work.

I know I can manually create relations with the dataset, but is that really
necessary? The XML-schema says it all how it should be nested. Or is it my SP
that doesn't return the info about how the two tables relate.

What is required for this to work? Can I get it to work by just using the
XML-schema generated for me, or do I have to add the relations between the
tables in code for this to work? Or should I change my SP to return the data
in another way (today it just simply are two selects from the two tables for
a specific company for example).

And, I'm also wondering another thing, which I just as well may ask. Why are
the tables in the dataset named "Table1" and "Table2" etc., instead of their
actual name as used in the SP? Is this the default behaviour that the
tablenames doesn't get returned from the SP in the two resultsets?

Well, if anyone has any hint, please let me know. But, I would like to avoid
adding relationsinform ation for the tables in the code. It really must be
possible for that to be returned automatically from the SP, or I mean, the
actual XML-schema is perfectly fine in defining how the tables should relate?
Or can't it map XML-schema to tables by the names if they are the same?

Thank you for any help!