Home » Microsoft Technologies

Using an XML Schema to Create a DataSet and Load from Relational Database

I am attempting to create a Web service which returns a dataset (or even an XML document) that is valid against a particular schema. The schema only has a top element, say, Employees and multiple Employee elements inside which contain elements for name, email, telephone. ID is the only attribute to the Employee element. I am attempting to fill the dataset and return the dataset (or an XML document) to the client.

I have tried to create a dataset from the schema using the XmlReadSchema, and then create a dataadapter that fills the table in the dataset. During debugging, it appears that the datatable gets created, the dataadapter fetches the data but if I fill to the table created by the XmlReadSchema, it causes an error:


System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.


If I look at the Constraints collection, the count = 0. If I fill a new table name "on the fly" (da.fill(ds, "DifferentTable")) it fills the dataset. I am returning a single row from the database in a table and with data specifically made to conform to the schema. I have done some reading, and I think a WCF solution is a direction I should go, but I need to provide solutions while I learn WCF.

What would be the best way to ensure that the data I return from an SQL Server table goes out the Web service formatted according to a particular schema?

(Note: I did review an article on Extending WebMethods with Schema Validation from a 2003 July article, but could not see the ValidationExtension type in the current .NET.)

Thank you for your advice.



3 Answers Found


Answer 1

Clearly you're having an ADO.NET problem right now, so I'll move your question to that forum.

I actually recommend against returning  a DataSet in any case. That is a type specific to .NET, so only a .NET client  will be able to consume it properly. In fact, there have been issues when the client and the service  are running different .NET versions.

Instead, you should return  a simple "data transfer object": an object that is all data  and no behavior. For instance:

public class GetEmployeesResponse
  public List<Employee> Employees {get;set;}

public class Employee
  public string ID {get;set;}
  public string FullName {get;set;}
  public string Email {get;set;}

Answer 2

Hi, John:

Thanks for your reply. I picked it up shortly after you posted, but then I began to research a DTO. The only references I could find were on how to do it with a Dataset. Plus, I've been busy. I see your solution  got marked as the answer, but I don't see from my references or from your example how this ensures that the returned "DTO" is compliant with my schema.

Do you have any good references from me to read about it?

I've also been trying to balance time with digging into WCF, but that still seems a bit distant right now.


Answer 3

Simply mark up the DTO class with the attributes necessary to force it to conform  to your schema  when it serializes.

If you're looking for something that references the schema and says, "go be like this", then you're not going to find it. The closest you can get is to take the schema(s) and run them through the xsd.exe program to produce classes which, when serialized will conform to the schema.

John Saunders
WCF is Web Services. They are not two separate things.
Use WCF for All New Web Service Development, instead of legacy ASMX or obsolete WSE
Use File->New Project to create  Web Service Projects


I'm creating a simple database app, and am having a few problems updating the schema in the dataset to correspond to the database schema I've created in SQL Server Management Studio Express.

I was previously using a local SQL Server Compact Edition database I'd created within the project using Visual C# Express edition, and I would simply update the dataset by going into the Data Sources view and right click > Configure Data Source with Wizard.

However, with the new project I'm trying, I've created a database in SQL Server Management Studio Express and want to use this database. However there are a number of issues relating to this:

1. I've noticed that if I've opened SQL Server Management Studio and connected to the server, I am unable to connect to the database from Visual Studio. I get the error: Cannot open user default database. "Login Failed". I'm also unable to log onto the database I want to in the Modify Connection because it is in use.

I've checked out the following link: http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/65555594-a63e-4138-bfab-08e260115353/. This solution recommends using the query analyser, but I can't find this, and from the internet searches I've done it looks like it isn't available for SQL Server Manager Express.

I've tried disconnecting from the database, and even stopping it altogether, but this doesn't fix it. Restarting the computer fixes it, but this isn't a practical solution. I'm sure this problem must come up a lot, but I'm new to SQL Server Management Studio.

2. The database I'm connecting to seems to reset itself everytime I re-open Visual Studio. I'm not sure if I've set something up correctly, but I originally set up the connection to point to the actual database, however every time I re-open Visual Studio, when I go to Modify Connection, it is pointing at a local copy.

3. When I update the database schema in SQL Server, and then try to update my dataset in Visual Studio using the data source wizard, it doesn't seem to catch everything. It seems to pick up some changes to the columns and relations, but I've changed the type of some of the names from nvarchar(50) to nvarchar(100), but in the dataset in Visual Studio, they still have a max length of 50. (I've made sure that the connection is pointing to the correct database.)

I'm new to SQL Server, so any help would be appreciated.



Hi all

I have XML file

<?xml version="1.0" encoding="UTF-8" ?>
- <env:ContentEnvelope xmlns="http://???.com/metadata/2009-09-01/" xmlns:env="http://???.com/Envelope/2008-05-01/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" pubStyle="FullRebuild" majVers="1" minVers="0.0">
- <env:Header>
- <env:Info>
- <env:Body contentSet="MetadataSystems" majVers="1" minVers="0.0">
- <env:ContentItem action="Insert">
- <env:Data xsi:type="CommodityItem">
- <Commodity activeFrom="1900-01-01T00:00:00">
<CommodityUniqueName>Acid Oils</CommodityUniqueName>
<CommodityName effectiveFrom="1900-01-01T00:00:00" commodityNameType="404502" language="505074" commodityNameMetadataViewId="404300" commodityNameSequence="1">Acidic oils- an agricultural commodity</CommodityName>
<CommodityName effectiveFrom="1900-01-01T00:00:00" commodityNameType="404500" language="505074" commodityNameMetadataViewId="404300" commodityNameSequence="1">Acid Oils</CommodityName>

and I need to load it in database:

CREATE TABLE Commodity (
CommodityId bigint NOT NULL,
CommodityUniqueName nvarchar(128) NOT NULL,
ActiveFrom datetime NOT NULL,
ActiveTo datetime NULL

CREATE TABLE CommodityName (
CommodityId bigint NOT NULL,
CommodityNameType bigint NOT NULL,
CommodityNameMetadataViewId bigint NOT NULL,
CommodityNameSequence bigint NULL,
EffectiveFrom datetime NOT NULL,
EffectiveTo datetime NULL,
Language bigint NOT NULL,
CommodityName nvarchar(4000) NOT NULL


Please help create mapping xml schema



I've got a SQL database with a table named contact with different column such Name, Address, email ...

I would like to generate a XML file based on the table data:


     <name> John</name>

    <address> 100 main street </address>



After some research it looks like I can use a dataset from that table to create my xml.

Being a beginner I cannot find the right steps / syntax to do so.

Is there any tutorial showing how to work with dataset and XLM?

Thank you for your helps 


After creating a datasource and generating a dataset, my database has changed. 
How can I "refresh" the dataset so it sees these changes.
The only thing I have been able to do is delete the .xsd file from soultion explorer.
But then I have to recreate queries and/or datasource code I have created.

Is there a better way?


Our products work from a database that has been in development for more than 10 years. At times very bad decisions were made on how to store data. For example one-to-many are often decribed using "piped fields" rather than a seperate table. For example:

OWNERS field in the Media table might contain: FRED|BILL|DAN|DAVE

rather than having a MediaOwners table linking to an Person Table or something more standard.

Work is in progress to remove some of these bad decisions but I don't think they will ever be truly gone.

Are there any features available in the entity framework that might make working with this kind of sitatuation more bearable. Specifically if anyone has a nice solution to the "piped field" issue that would be great - but general comments, ideas and experiences of working with a less than perfect database schema/non-relation data source would be welcome.



Hi ,

I want to load minimum 150MB of XML into dataset,

simple way of doing that is

XmlDataDocument xmlDatadoc = new XmlDataDocument();

But i feel on Lagre size message this code make some performance issue.

Can any one helpout me on this ?

I'm trying to open a dbproj created by a coworker and am getting this error.  I've created database projects on my own and am having no problems.  Just when I open his I get the error.  Is there a dbschema file or something that he needs to check in?

I need to create the ReportDataSource (for a Microsoft Report) object from an xmlSchema during runtime.

I can get the data to show if i use a DataSet created in the project itself: e.g.

ReportDataSource ds = new ReportDataSource("DataSet1_MostRuns");
ds.Value = new DataSet1TableAdapters.MostRunsTableAdapter().GetData();

but i need to load the dataset at runtime and change the connection string as well if possible.

When i've tried this so far the schema loads but the tables contain no rows.

How do i get the DataSet to populate the rows?

And also how do i change/set the connection string?

Any help would be appreciated. Thanks


I have below sample data from a system,

Table X

Col_Name                DataType

 systemName            string

 SystemVersion         Decimal

 SubmitedOn             DateTime

Now, I m creating below XML by above data in C#,


  <SystemName Value="Timesheet" Name=""></SystemName>

  <SystemVersion Value="1.0" Name=""></SystemVersion>

  <SubmitedOn Value="08/31/2010" Name=""></SubmitedOn>


  Now I need to prepare XML SCHEMA for above XML by C# and register the same in SQL 2005...so that nxt time we can validate above XML.

Please someone suggest me how to do above functionality....creating schema at runtime by C# and register the same schema into SQL 2005 database.



I am generating below schema at run time in C#,

Is there any way to create a XML Schema in SQL 2005 for this run time generated schema???

<?xml version="1.0" encoding="utf-16"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="Address">
    <xs:element name="Name"type="xs:string" />


How to create a generic schema for taking different different xml files....

Can you explain me step by step for this.


I ‘m looking for advice on designing an OData service.   I want to build a single WCF Data Service which combines data from a relational source (SQL Azure) with data from a non-relational source (in-memory objects).  The thing is that at least one of the classes will actually have the data from both sources, so that it will be possible to “navigate” from a record in a Sql Azure table to a set of objects drawn from the in-memory data source.  Should I be using the Reflection provider over POCO classes, and populate all my objects manually?  Or can I make use of Entity Framework 4 POCO capabilities and retain some of the power of EF at least for the Sql Azure stuff, while manually building the non-Sql Azure data, a kind of hybrid approach?


HI Guys

i have Table called EMP(UserID,EmpNum,EmpDT,DeptNO)
Data in the Table is


i want to create an xml file for each DeptNo

i dont know how to add this Header Tages

in the Header tage the Header Store=DeptNO and Business_Date=todaysDate

<? xml Version ="1.0" encoding="iso-8859-1"?>
<Header Store="0234" Business_Date="20100626'/>


the out put file should be like this

<? xml Version ="1.0" encoding="iso-8859-1"?>
<Header Store="0234" Business_Date="20100626'/>




I keep getting a "Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct." Details: "Timeout expired. The timeout period elapsed prior to completeion of the operation or the server is not responding. The statement has been terminated"

Time out on DataSet Properties is set to default "0" which should equate to "no timeout". Under tools - options - I have disabled or bumped up all things related to time outs or connections.

The dataset is calling a sproc, which takes about 35 secs to generate. We are currently migrating everything to 2008 R2 and deploying to Sharepoint. So I really need to get this working.

Last point of interest. If I load the dataset and create the report in VS 2005, then pull the rdl into the 2008 R2 report project.. no problems at all.

Any ideas on what I can check to resolve this supposed "time out" issue?




(SSIS 2008)

I have a table with the following structure

CREATE TABLE [dbo].[TimeSeries](
 [TimeSeriesId] [bigint] IDENTITY(1,1) NOT NULL,
 [TimeSeriesEffectiveDate] [datetime] NOT NULL,
 [content] [xml] NOT NULL

on which I would like to transform the xml data field into a relational table.

In my Ole DB Source:

SELECT TimeSeriesId, TimeSeriesEffectiveDate, content AS XMLContent
FROM dbo.TimeSeries
WHERE (lastupdate BETWEEN ? AND ?)

the structure of the xml data field is like this:

 <values v0="25" v1="25" v2="25" />
 <qualities v0="4" v1="1" v2="2" />

To convert this into a tabular form I have an OleDB Command where I will execute this

declare @xmlValues as xml
select @xmlValues = ?;
with V (id, ts, inx, val1) as 
 ? as id,
 convert(smalldatetime, ?) as ts,
 convert(int, substring(x.value('local-name(.)', 'varchar(255)'), 2, 255)) as inx1,
 convert(decimal(18,4), x.value('.', 'varchar(255)')) as val1
 from @xmlValues.nodes('TS[1]/values[1]/@*') as T(x)
, Q(inx, val2) as 
 convert(int, substring(y.value('local-name(.)', 'varchar(255)'), 2, 255)) as inx,
 convert(decimal(18,4), y.value('.', 'varchar(255)')) as val2
 from @xmlValues .nodes('TS[1]/qualities[1]/@*') as T(y)
select V.id, V.inx, V.val1, Q.val2, DATEADD(hh, V.inx, V.ts) as ts 
from V left outer join Q on V.inx = Q.inx

The problem is the xml field. In the Ole DB command I can see that it is treated as a
Unicode text strean [DT_NTEXT] but if I create an External column of type NTEXT in the Ole DB Command task to hold the xml input I get the error VS_NEEDSNETMETADATA when I execute the task. I have tried to add a Data Conversion task to convert to other data types, but without luck.

What can I do to make this work?

/Paul S


I am just getting starting with the Database Project in VS 2010 Ultimate. I am current designing my tables, etc in SSMS, and then creating each object by hand in the DB project. This is very cumbersome, to say the least. Is there a way to get the project to look at the current schema of the database (as created in SSMS), the current scripts, and generate the changes? That would makes things much easier, given that we don't seem to have a GUI tool in the database project.



I'm definately missing something from the whole VisualStudio/Ado side of things ... either that or microsoft has missed a clear (and simple?) way to close the loop in they're tools.  This rant is mainly related to Visual Studio, but what i'm tryng to acheive is mainly ADO.NET related (hence this forum chosen for posting).

What i want to do is create a complete relational dataset using the data set designer in visual studio; that way i get a strongle typed class hierarchy based on the data relations i create.  Then i want to push that back into an SqlServer and recreate the entire dataset as a database.  This is for setup/test, so any changes required to the database can be done visually using the designer, code can be added to populate the dataset with dummy data for test, and the entire database can be completely dropped and rebuilt each time to ensure there is nothing hanging on.  Sounds reasonable to me (until you try to do it), especially as you can do it the other way, create a database visually in studio using database diagramming, then create an empty dataset and load it from the database, populate it and push the data back - only problem is that the dataset won't be strongly typed.  I can't even find a way to load a new database from XML (or i could dump the dataset to xml and do it indirectly).

Is there any way to create a strongly typed dataset from an existing database?

Is there any way to create a database from a dataset?

Is there any way to create a database from xml? 

It seems that in visual studio i can vsually "draw" a database (using database diagrams in the DataConnections) and i can "draw" a dataset, but the two can never be related - to get this to work i'm going to have to draw it twice (or find a way to generate the SQL needed to create the database from the information stored in the dataset).

Any pointers on creating relations between tables in a database using the relations collection in the dataset?

As always, all opinions, pointers, help, etc. etc, are welcome and appreciated.

 Thanks, Andrew.

P.S. No spell checker on the forum?



I have created an application in which I have to make a report with ReportViewver from Microsoft.
Everything ok until now. I have created the report and for this report I have created a dataset. This dataset contains data for every month in every year from 2010 to 2015. With this dataset I am populating a graphic but my problem is that I want to select the year and create graphic for one year.

I do not know how to insert a parameter in the dataset. In sql is very easy by in Access is different and if I try to put something like ...' where year_column=@year_parameter' I receive an error.

Can somebody help me?


Hi all,

I am reading xml file using dataset.readxml() method. . . after this i want to send this data to the database. I want c# code to create a database in sql sever and push watever data in the dataset to that created database.... i am dealing with many xml documents... some could be of same format.. lets say... if 2 docs of same format exists... then it shud not create new database or new tables.. it shud push the data to the suitable format...as i am a newbie its a bit confusing for me... i dont even have any idea on how to proceed with this... please reply if you know the answer for any part of the requirement... any help is very much appreciated..





Hello all, this is my first post to MSDN. I hope one of the clever people on here can help me out. I've been trying to teach myself relational database theory and I don't really want to go too deeply with it - it's only a hobby! Anyway, I've been reading "The Essence of Databases" by F. D. Rolland and trying to create my own database using the "entity/relationship" model described in the book.

I thought I had a grasp of it and was going well until I came to put my actual database into SQL Server. What I find now is that I have too many foreign keys and I can't fill out rows in my tables because they reference foreign keys in other tables that haven't yet been defined. I'm kind of chasing my tail. It's pretty hard to describe in words, so please have a look at my "design document" here: http://cid-f303e91efe3894cb.office.live.com/self.aspx/.Public/Database%20Design.pdf?lc=2057

To give you some background, I thought it'd be nice to create a GUI front-end for a game-emulator (I know this has been done many times before). I'm trying to tie multiple files together in a database so that I can look up a game in the database, then find what system (only one system per game), emulators and images are associated with that game. However, I'd also like to be able to look up a system and see what games are related to it. I'm probably hugely over-complicating things with my design! Any help with untangling the mess that I've created would be greatly appreciated.

Thanks in advance,



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure