XSD, Dataset, SQL Server linking

  • Thread starter Thread starter SimeonD
  • Start date Start date
S

SimeonD

Hi
I will need to send some data to a third party on a weekly basis. They have
sent me an XSD file, Policy.XSD.
I'm using VB 2005. I added the XSD to my project, set the CustomTool to
'MSDataSetGenerator'. Visual Studio then created a Policy.Designer.vb file.
My data is stored in SQL server.

I'm looking for some guidance on the best way forward. I don't know much
about XML and XSD, but I'm ok on ADO. (yes, I know Dataset is related to
XSD, but I never had to worry about it before)
So some questions

1) In my datasources window, I have the SQL Server datatables. Is there any
quick way to get the data from there into the Policy dataset?

2) I've experimented adding data to the Policy dataset. Like below. The
dataset seems to consist of several datatables, with relationships. This
relationships are now in Policy.Designer.vb code. Is there an easy way to
print off the relationships?
Its making it difficult to test, when certain elements have to link
elsewhere.

Dim dsComm As New CommPolicy.NewDataSet
Dim dtBusiness As CommPolicy.NewDataSet.BusinessDataTable =
dsComm.Business
Dim rowBusiness As CommPolicy.NewDataSet.BusinessRow = Nothing

rowBusiness = dtBusiness.NewBusinessRow
rowBusiness.Business_Id = 1
rowBusiness.Turnover = 2000
dtBusiness.AddBusinessRow(rowBusiness)

3) I'm unclear on how I would a new entry. In the example, I've added a new
row for one policy. How would I add a row for a second policy?


4) If you're done this kind of thing before, can you give me some hints on
how to make things easier.



Thanks!
Simeon
 
Hi
I will need to send some data to a third party on a weekly basis. They
have sent me an XSD file, Policy.XSD.
I'm using VB 2005. I added the XSD to my project, set the CustomTool
to 'MSDataSetGenerator'. Visual Studio then created a
Policy.Designer.vb file. My data is stored in SQL server.

I'm looking for some guidance on the best way forward. I don't know
much about XML and XSD, but I'm ok on ADO. (yes, I know Dataset is
related to XSD, but I never had to worry about it before)
So some questions

1) In my datasources window, I have the SQL Server datatables. Is
there any quick way to get the data from there into the Policy
dataset?
Click to expand...

Add a table adapter to the data table representation in the DataSet.
Create a query that pulls the info you wish to present to the client in
the table adapter.
2) I've experimented adding data to the Policy dataset. Like below.
The dataset seems to consist of several datatables, with
relationships. This relationships are now in Policy.Designer.vb code.
Is there an easy way to print off the relationships?
Its making it difficult to test, when certain elements have to link
elsewhere.

Dim dsComm As New CommPolicy.NewDataSet
Dim dtBusiness As CommPolicy.NewDataSet.BusinessDataTable =
dsComm.Business
Dim rowBusiness As CommPolicy.NewDataSet.BusinessRow =
Nothing

rowBusiness = dtBusiness.NewBusinessRow
rowBusiness.Business_Id = 1
rowBusiness.Turnover = 2000
dtBusiness.AddBusinessRow(rowBusiness)
Click to expand...

This is the hard way, since you have the XSD.
3) I'm unclear on how I would a new entry. In the example, I've added
a new row for one policy. How would I add a row for a second policy?
Click to expand...

I am not sure you have to do this, as you have an XSD (strongly typed
DataSet) and can add a table adapter to fill it with the info you need.
4) If you're done this kind of thing before, can you give me some
hints on how to make things easier.
Click to expand...

Provided you are only doing as you say (providing regular data dumps to
a client), the table adapter route is the easiest. There are "more
performant" ways of handling this, of course, but performance is
probably not your biggest concern here (ie, it is not hindering you).

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************
 
Hi Greg
Thanks. As you say, performance is not important here. Making it easier for
me is!
I was delighted with the TableAdapter idea. However, I don't have that
option.
When I go to design from the Data Sources window, I get the graphical
representation for the XSD.
So instead of datatables, I seem to have Elements, Simple Types and Groups.
Right clicking gives me the option of adding Elements, Attributes or Groups.
This is all new to me, so I'm not sure what this is. Maybe some kind of
Class representation?
I don't want to post the full XSD here, below is a section from it. It it
possible because of the way the XSd is structured that I can't add a table
adapter?

<!-- Sum insured for contract works -->
<xsd:element name="WorksTempWorks" minOccurs="1" maxOccurs="1">
<xsd:annotation>
<xsd:documentation>
The insured has the option of selection either 500,000
or 1,000,000.
</xsd:documentation>
</xsd:annotation>
<xsd:simpleType>
<xsd:restriction base="LimitOfIndemnity">
<xsd:enumeration value="500000" />
<xsd:enumeration value="1000000" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>


Thanks
Simeon
 
Hi Greg
Thanks. As you say, performance is not important here. Making it
easier for me is!
I was delighted with the TableAdapter idea. However, I don't have that
option.
When I go to design from the Data Sources window, I get the graphical
representation for the XSD.
So instead of datatables, I seem to have Elements, Simple Types and
Groups. Right clicking gives me the option of adding Elements,
Attributes or Groups. This is all new to me, so I'm not sure what this
is. Maybe some kind of Class representation?
I don't want to post the full XSD here, below is a section from it. It
it possible because of the way the XSd is structured that I can't add
a table adapter?
Click to expand...

I made an assumption the XSD was a DataSet XSD, which was wrong. THere
are a couple of ways to tackle this:

1. You can use the XSD.exe tool to create a DataSet XSD from the XML
Schema:
http://snurl.com/rvdeg

2. You can also create objects from an XSD using xsd.exe.

This will simplify the object generation. You then fill the objects and
use .NET serialization to output to the XSD you desire. After
serialization, you can put the data in an XML document and add the XSD
to the schema collection to verify it is valid. You don't have to
necessarily do this ongoing, but it is a good sanity check before
sending the first one to your partner.

3. Another option is to create an XML file that adheres to the schema
and then populate it using the XML classes. This is more painful and has
a steeper learning curve. There are some third party tools to help with
this task. Microsoft made some, as well, and you can also us ethe schema
bits to generate, but you are largely on your own. this is the most
"precise" method, but it has a steep learning curve.


----

As far as validating (advised), when you create the XML document, most
likely through serialization, you can double check it against schema by
adding the XSD to the schema collection. You do not necessarily have to
do this every time you send the file, but it would be advisable during
debugging.

As a side note, Microsoft was going to ship a better XSD designer in VS
2008, but it never got past CTP AFAIK. I have not examined SP1 to see if
it made it there, but without public clamour, I would assume it died.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************
 
I tried using XSD.exe, first tried using the class option. This meant that
the enumeration within the XSD was lost though.
Using XSD.exe tool with the dataset option keeps the enumeration. I also
found that just adding Policy.xsd to my project did the same thing, without
the need for the tool.
Just had to make sure I set Custom Tool = MSDataSetGenerator

I now have Policy in the DataSources pane. There are no table adapter
available though.
It seems to be different to the normal data sources window.

With regards to validating - I found some code at
http://www.codeproject.com/KB/cpp/Simple_XML_Validator.aspx
It that what you mean?

Thanks
Simeon
 
I tried using XSD.exe, first tried using the class option. This meant
that the enumeration within the XSD was lost though.
Using XSD.exe tool with the dataset option keeps the enumeration. I
also found that just adding Policy.xsd to my project did the same
thing, without the need for the tool.
Just had to make sure I set Custom Tool = MSDataSetGenerator
Click to expand...

Custom Tool? I completely forgot about that. Have a funny story, but it
is along, so probably best for another time. ;-)
I now have Policy in the DataSources pane. There are no table adapter
available though.
It seems to be different to the normal data sources window.
Click to expand...

If you run classes, it creates classes, not datasets. If you want a
dataset, there is that option in xsd.exe, however.

With classes, you will simply create a new class for the data bits and
fill it and add to the collection. To get the XML, you will serialize
and then send the serialized string to the client.

If this does not make sense, try xsd.exe with the DataSet option, as you
can add the table adapter to a created DataSet.
With regards to validating - I found some code at
http://www.codeproject.com/KB/cpp/Simple_XML_Validator.aspx
It that what you mean?
Click to expand...

That is certaily an option. Xml Spy, and similar tools, can also
validate against XSD.

The easiest validation in .NET is take the serialized string and feed
into an Xml validating reader. This can be done from a file, as in the
following:

http://snurl.com/rwnso

But note that readers can also take other forms of streams, so you can
load the string into a memory stream and feed that to the reader to
validate without saving the file.

Whether you save the file or not depends on your client (do they want
the info sent via a web service or dropped via FTP, for example). If you
do save the file, you can save and then use the code in the sample.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************
 
Thanks Greg.
One last question, and I'm done. Any idea why the xsd design looks so
different from what I'd usually see? I've attached a screenshot in a
different post.
 
Thanks Greg.
One last question, and I'm done. Any idea why the xsd design looks so
different from what I'd usually see? I've attached a screenshot in a
different post.
Click to expand...

I will have to look at it a bit later, but I can think of a few
possibilities:

1. The XSD in question has created a mess and you have to head another
direction. Figured I would get worst case in first.

2. The XSD in question has elements MS understands but cannot render
properly in the visual tool.

3. It is rendered correctly, but the constructs are different than you
would normally see in a DataSet.

When the XSD designer first came out it was a bit more XSD focused. As
strongly typed datasets dominated, the designer focused more on MS-XSD,
if you will, and dropped out other XSD types.

One thing you can do to validate the XSD for the DataSet is use a tool
(trial version?) like XML Spy to ensure it is fine.

One thing I would definitely do is try populating what you have with a
record or two and then validate against the original XSD. If it does not
work, you may have to take another route.

peace and grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************
 
This particular format is proving VERY difficult to work with. I can't add
columns or tables easily, which must be some function of the XSD.
I think I'll look into converting it to the standard format.
 
Did a DataSet.WriteXMLSchema.
Opened the resulting XSD in a new project, and now I can work it like
normal.
However, I lost all the comments and the enumeration values.
I can live with that, as the enumeration didn't seem to work anyway.
But I'd like to add the comments back.

How can I do a WriteXMLSchema where the comments are copied?
How do I add a comment do a standard schema anyway?

Thanks very much for your help so far- its been an education!
 
Did a DataSet.WriteXMLSchema.
Opened the resulting XSD in a new project, and now I can work it like
normal.
However, I lost all the comments and the enumeration values.
I can live with that, as the enumeration didn't seem to work anyway.
But I'd like to add the comments back.

How can I do a WriteXMLSchema where the comments are copied?
How do I add a comment do a standard schema anyway?

Thanks very much for your help so far- its been an education!
Click to expand...

The comments will not be saved. You can either replace them or not,
depending on how much they help.

Personally, I would keep both the generated XSD and the original. Use
the generated (or generated objects) to create the XML to send and
populate, etc. Use the original to validate (at least during
development, maybe farther). The original can also be used for the
comments, if you need to look at them.

I wish there was a more complete answer, but MS has invested heavily in
XML as they see it. This means the DataSet form of XSD is well
supported, while the XML Schema is not. In most cases, this is not an
issue.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************
 
Hi
One more bootnote on this.
If you right click on the XSD within Visual Studio 2005, you get the various
options for opening the XSD. Dataset Editor is the standard, but there are
many more available.
XML Schema Editor was the one used here, which confused me so much at the
start.
Regards
Simeon
 
Hi
One more bootnote on this.
If you right click on the XSD within Visual Studio 2005, you get the
various options for opening the XSD. Dataset Editor is the standard,
but there are many more available.
XML Schema Editor was the one used here, which confused me so much at
the start.
Click to expand...

XML Schema is the XSD standard, from a non-microsoft specific standpoint.
The XSD Schema editor is not as well known, as not as many people jumped on
board. I am also not sure it is 100% up to date.

The DataSet editor is more well known, as Microsoft has their own version
(subset) of XML Schema that is called a DataSet.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************
 
Gregory A. Beamer said:
XML Schema is the XSD standard, from a non-microsoft specific standpoint.
The XSD Schema editor is not as well known, as not as many people jumped
on
board. I am also not sure it is 100% up to date.

The DataSet editor is more well known, as Microsoft has their own version
(subset) of XML Schema that is called a DataSet.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************
Click to expand...


But if you want to use attributes, or change the way your XML output
displays, then the XML Schema editor seems to be only place to go.
I have another question on here - "Help with XML output" - I reckon what I
wish to achieve is not possible through the DataSet editor.

Thanks
Simeon
 
But if you want to use attributes, or change the way your XML output
displays, then the XML Schema editor seems to be only place to go.
I have another question on here - "Help with XML output" - I reckon
what I wish to achieve is not possible through the DataSet editor.
Click to expand...

If you want to hold onto the bits of XML Schema that are not inherent in
the database, then the DataSet route is out. i tis hte easiest route, in
many ways, as there is lots of literature on it (Microsoft being heavily
vested), but if it does not work for you, it does not work for you.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************
 
Back
Top