Question Work with XSD

Simeon

Member
Joined
Sep 14, 2009
Messages
13
Programming Experience
Beginner
I have to output data from a SQL Server database. I've been given an XSD.
using VB 2005.
Have not worked with supplied XSD before, so I'm a bit unsure about what to do next.


1) I've added the XSD to a VB project. The dataset seems to have several datatables, broken down like
Policy
____Premium
____Proposer
_________Name
_________Address
____RiskDetails
_________Business
_____________Trade
_____________Employees
_____________Wage Details
_________Clauses

So Business is a sub datatable of RiskDetails, and Trade is a sub of Business.
Going to the data sources window, I can see the breakdown as above. However, clicking the design box opens up some sort of schema design window. There are no options to add a table adapter.
What would be the best way to add the data?

2) Validation
The XSD also has extra information about the data, such as that below. Which I think means that the Category Code must be one of the values in the list. How should I use this information?
Is doesn't seem to come into the dataset.
Let says I add all my info to the dataset, and then output the XML. Is there a way to validate that the XML I've produced conforms to the XSD format I've been given?


<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:length value="3" />
<!-- Category code for Clerical Staff -->
<xsd:enumeration value="119" />
<!-- Category code for Manual Principals -->
<xsd:enumeration value="111" />
<!-- Category code for Manual Employees -->
<xsd:enumeration value="006" />
<!-- Category code for Machinists (woodworking cover) -->
<xsd:enumeration value="125" />
</xsd:restriction>
</xsd:simpleType>


Thanks
Simeon
 
If you fill your dataset and then simply call dataset.WriteXml("strFilePathAndName") it will create the xml file for you and will file the data structure of the xsd dataset. You can use IncludeSchema if you want to output the dataset structure itself (defining variable types and properties of every field).

The biggest concern really is formating of the output. If you have multiple tables they will print out one table at a time. So your first table will print out all its records before it starts any of the records of your child table. In order to print out nested child tables such as for each RiskDetails record would print the nested child records of business associated to that specific business record, you also have to create relations between the tables and ensure that "nested relation" is checked.

Your helpfile should provide plenty of info and examples about TableAdapters. My own personal preference is to put my queries in stored procedures and create my own subs with DataAdapters to fill and control my datasets.
 
Last edited:
I don't want to output the data structure. The customer already has that, as they gave me the XSD. Which I should probably have mentioned....:)

My question is more - how do I make the most use of the XSD they gave me?
Since I can't create table adapters on it, for whatever reason, I did manage to get something going, see my code below.
The relationships are in the XSD, so once I set the PolicyID, the 'nested' child tables are taken care of.
I reckon I'm ok now to generate the XML.
I still need to know how to validate my output against the XSD. Any ideas?
Thanks




Dim dsComm As New CommPolicy.NewDataSet

Dim dtPolicy As CommPolicy.NewDataSet.ESPolicyDataTable = dsComm.ESPolicy
Dim rowPolicy As CommPolicy.NewDataSet.ESPolicyRow = dtPolicy.NewESPolicyRow
With rowPolicy
.ESPolicy_Id = iPolicyID
' etc

End With
dtPolicy.AddESPolicyRow(rowPolicy)


Dim dtPremium As CommPolicy.NewDataSet.PolicyPremiumDataTable = dsComm.PolicyPremium
Dim rowPremium As CommPolicy.NewDataSet.PolicyPremiumRow
rowPremium = dtPremium.NewPolicyPremiumRow
With rowPremium
.ESPolicy_Id = iPolicyID
.LevyAmount = 500
End With
 
I'm not sure what you mean by validate the output then, the typed dataset (xsd) already has each of the fields defined; therefore it will not allow you to fill it with incorrect data/datatypes.

I have no clue what the coding above is supposed to be doing but I will show an simple example of filling a dataset table from data queried from Sql Server. In this example, I have created my select statement and added it as a stored procedure in my database. I simply will call the stored procedure from my vb app to fill the dataset table.

VB.NET:
[COLOR="Blue"]Dim [/COLOR]ds [COLOR="blue"]As New [/COLOR]MyTypeDataSetNameHere

[COLOR="blue"]Using [/COLOR]con [COLOR="blue"]As New [/COLOR]SqlConnection(g_strDbConnectionString)
    [COLOR="blue"]Dim [/COLOR]cmd [COLOR="blue"]As New [/COLOR]SqlCommand
    [COLOR="blue"]Dim [/COLOR]da [COLOR="blue"]As [/COLOR]SqlDataAdapter = [COLOR="blue"]Nothing[/COLOR]
   
    cmd.Connection = con
    cmd.CommandText = [COLOR="DarkRed"]"spTable1Select"[/COLOR]

    da = [COLOR="blue"]New [/COLOR]SqlDataAdapter(cmd)
    da.Fill(ds, [COLOR="darkred"]"tbl1Name")[/COLOR]

    da.Dispose()
    cmd.Dispose()
[COLOR="blue"]End Using[/COLOR]

[COLOR="seagreen"]'Just to display the datasets table results for viewing purposes[/COLOR]
DataGridView1.DataSource = ds.tbl1Name

[COLOR="SeaGreen"]'Outputting the results to an XML file[/COLOR]
ds.WriteXml(strFileName)

You dont need to create a seperate fill for multiple tables. You can add it all to the same procedure. For example if I have 3 tables within the dataset that I need filled, in my stored procedure I will add 3 queries, one for each table. The stored procedure can pass back multiple result sets so this is an easy way of getting multiple results with one call to the database.

However there does need to be an addition to the above coding to handle this. If the stored procedure returns 3 results, they would automatically be named "Table", "Table1" & "Table2" corrosponding to the order of your queries in the SP.

To associate these returned results, you will need to use table mappings to point each of these tables to the proper tables within your dataset.

VB.NET:
[COLOR="Blue"]Dim [/COLOR]ds [COLOR="blue"]As New [/COLOR]MyTypeDataSetNameHere

[COLOR="blue"]Using [/COLOR]con [COLOR="blue"]As New [/COLOR]SqlConnection(g_strDbConnectionString)
    [COLOR="blue"]Dim [/COLOR]cmd [COLOR="blue"]As New [/COLOR]SqlCommand
    [COLOR="blue"]Dim [/COLOR]da [COLOR="blue"]As New[/COLOR] SqlDataAdapter 
   
    cmd.Connection = con
    cmd.CommandText = [COLOR="DarkRed"]"spTablesSelect"[/COLOR]

    da.SelectCommand = cmd
    da.TableMappings.Add([COLOR="DarkRed"]"Table", "tbl1Name"[/COLOR])
    da.TableMappings.Add([COLOR="darkred"]"Table1", "tbl2Name"[/COLOR])
    da.TableMappings.Add([COLOR="darkred"]"Table2", "tbl3Name"[/COLOR])
    da.Fill(ds)

    da.Dispose()
    cmd.Dispose()
[COLOR="blue"]End Using[/COLOR]
[/QUOTE]
 
In short, xsd files (I call them DataLayers) allow you to create all of the needed DataTables (and their Adapters) from the various data sources (usually databases) for the app. What it allows you to do is have a Table from a Table/View/StoredProcedure/etc in a database (using a connection string obviously, the xsd designer lets you create these easily as well) and you can specify multiple adapter methods for that table. If you have a table where you get the data via a SELECT statement you can tell it to not generate the corresponding Insert/Update/Delete queries as well, meaning in code all you can do is fill the table.

What I like most about this is it puts all of the table and whatnot in a single place, then in code I simply create instances of the objects and go from there:
VB.NET:
Dim DT As new DataLayer.SomeDataTable
Dim DA As New DataLayerTableAdapters.SomeDataAdapter

Try
  'This DA might have more than 1 fill method, depending on how you set it up:
  DA.Fill(DT)
  'Or
  DA.FillBySomeID(DT)
Catch ex As Exception
  'Handle it here
End Try
You might learn some useful stuff from these:
XSD File - What is an XSD File? - Open XSD File Extension
Creating an XSD Schema using VS.Net Tools
 
My code shows working with "CommPolicy.NewDataSet" - the dataset which is created when I add the supplied XSD.

As regards validation - when I added the XSD as a class, CommPolicy.vb, all the enumeration data was lost.
However, if add it as a dataset, the enumeration data was kept. I'll be sticking with the dataset method then, but did find a tool to validate XML against an XSD at
CodeProject: Simple code to validate an XML file against a schema file (XSD).

Forgot about table mappings - will be useful in this case. I take it I add the parent tables first. For example - the Address table is a sub of Proposer. I take it that means I should add the Proposer table first?

Thanks for all your help so far, its really helping me.

One more question - how come I can't add a tableadapter to the XSD?
 
One more question - how come I can't add a tableadapter to the XSD?

As I said previously, I typically dont use them so JB would be able to give a better answer to this then me but I believe its because your typed dataset is not attached to a datasource in the designer.
 
My code shows working with "CommPolicy.NewDataSet" - the dataset which is created when I add the supplied XSD.

As regards validation - when I added the XSD as a class, CommPolicy.vb, all the enumeration data was lost.
However, if add it as a dataset, the enumeration data was kept. I'll be sticking with the dataset method then, but did find a tool to validate XML against an XSD at
CodeProject: Simple code to validate an XML file against a schema file (XSD).

Forgot about table mappings - will be useful in this case. I take it I add the parent tables first. For example - the Address table is a sub of Proposer. I take it that means I should add the Proposer table first?

Thanks for all your help so far, its really helping me.

One more question - how come I can't add a tableadapter to the XSD?
I have no idea why the xsd file you got isn't working for you, the xsd file in VS2008 has not only the xsd file itself, but also has 4 support files that are designer generated, which has the vb/c# code (whichever language your project is created in) that actually does the input validation and holds the code for all of the DataAdapters. It maybe that all you were given was the raw xml file (the .xsd file) and you weren't given the rest so you might have to make your own and use the xsd file they gave you as a reference.

As I said previously, I typically dont use them so JB would be able to give a better answer to this then me but I believe its because your typed dataset is not attached to a datasource in the designer.
The designer takes care of the data sources for you and each table in the xsd has their own connection string this means each table could come from it's own source. Not to say that one or more of the sources are corrupt or are simply not defined.
 
The designer takes care of the data sources for you and each table in the xsd has their own connection string this means each table could come from it's own source. Not to say that one or more of the sources are corrupt or are simply not defined.

Yes that is my point though, since the dataset was sent from an outside source its datasource may not be connected to his database.

For instance if I drag a table from a datasource into a typed dataset, it is attached to a datasource. If I create the table from scratch without connecting it to a datasource, a tableadapter would not be provided automatically.
 
I've attached what the XSD looks like in the designer. I've never seen this type of layout before. In the top corner of the 'datatables', you can see a letter. Hovering with the mouse tells me these are
CT - Complex Type
E - Element
A -Attribute
ST- Simple type

Right clicking just giving me any option to add a TableAdapter like it usually would. I can only add one of those types I listed, or a relationship.

Maybe the data adapter isn't available, because of the data types?
 

Attachments

  • XSD.jpg
    XSD.jpg
    25.1 KB · Views: 45
One thing I forgot. In my original question, I show the enumeration that came with the XSD. How can I use this data?
For example, I'd like to make sure my Trade Category is one those listed in the XSD. Anyone got a clue how I can do that?


VB.NET:
            <xsd:attribute name="Category">
                <xsd:annotation>
                  <xsd:documentation>
                            This attribute is used to classify the group of employees: 
                            As to whether the number is in respect of Clerical Staff, Principals, Other 
                            Manual Staff or Wood Workers (Codes are per ABI Code list 515)
                            </xsd:documentation>
                </xsd:annotation>
                <xsd:simpleType>
                  <xsd:restriction base="xsd:string">
                    <xsd:length value="3" />
                    <!-- Category code for Clerical Staff -->
                    <xsd:enumeration value="119" />
                    <!-- Category code for Manual Principals -->
                    <xsd:enumeration value="111" />
                    <!-- Category code for Manual Employees -->
                    <xsd:enumeration value="006" />
                    <!-- Category code for Machinists (woodworking cover) -->
                    <xsd:enumeration value="125" />
                  </xsd:restriction>
                </xsd:simpleType>
              </xsd:attribute>
 
Ive never seen anything like that either. It looks more like a bunch of individual defined variables rather then actual tables containing columns.
 
I did a dataset.WriteXMLSchema, added that XSD, and now I am able to work with it as normal.
However, it loses the enumeration as above.
I'd like to add comments to the XSD, for when I send it to the Client. Would that be something that any of you would do?
 
Back
Top