Question Create Dataset and Tables from XML

[ballybeg]

Hi all. Looking for some guidance so I can attack my latest project the best way from the outset.

I have an XML file that has a basic hierarchical structure about horse racing. It has a summary of the tracks, some data about each track, then a list of all the races at each track racing that day. So lets say there are 10 tracks, and for each track there are 8 races. This is a snapshot of the data....

-<Meetings xmlns="" Jurisdiction="VIC">
    -<Summary MID="MR_20110801">
       <VenueName>ECHUCA (VIC)</VenueName>
       <Type>R</Type>
       <Events>10</Events>
       <Track>DEAD </Track>
       <Weather>FINE </Weather>
        -<Races>
          -<Race RID="MR_20110801_01">
            <Name>BORDER INN HOTEL/MOTEL 3YO MDN</Name>
            <Distance>1009m </Distance>
            <Class>MDN </Class>
            <RunnerNo Acceptors="13">11</RunnerNo>
            <JumpTime>11:08</JumpTime>
            <TimeToJump>-10:18:08</TimeToJump>
            <RaceStatus>Processed</RaceStatus></Race>
          -<Race RID="MR_20110801_02">
            <Name>PILLAR OF HERCULES 3YO MDN PLT</Name>
            <Distance>1009m </Distance>
            <Class>MDN </Class>
            <RunnerNo Acceptors="13">8</RunnerNo>
            <JumpTime>11:38</JumpTime>
            <TimeToJump>-09:48:08</TimeToJump>
            <RaceStatus>Processed</RaceStatus>
            </Race>
          etc......

I have written the code to pull down the data into a data store, but I dont know the BEST way to put the data into a set of tables. MY first thought was to traverse the nodes and add rows into tables but I figure there must be a native VB process that reads the xml and puts it into the the appropriate table.

The guidance I am looking for is how to code up the process for getting the data into a set of tables that match the xml. So lets say I have a Summary table, I would like all the data about each track to go into that table. Then I would have a Races table and have the data for each race go into that.

I'm really stuck at how to attack this thing. What I have done already is import the xml into Access to create the tables (which was a breeze!) and then link that data source to my project. So I have the tables already built.

However I figure there must be a way to create the tables on the fly directly from the XML...I would prefer to learn how to do that as there are other xml files I need to use as well.

All help greatly appreciated.

ballybeg

 

[JohnH]

DataSet.ReadXml Method (String) (System.Data)

You can also go one step further and use xsd.exe to generate a strongly typed dataset schema from the xml file. For example the command "xsd file.xml /d" will generate a "file.xsd" that you add to project.

 

[ballybeg]

Thanks John, I am already able to pull the data down and store it my data set...I want to know how to create and load the tables dynamically from that dataset.

I have already added the xsd into the project, can I create tables from that spec?

ballybeg

 

[JohnH]

I want to know how to create and load the tables dynamically from that dataset.

For an empty untyped DataSet instance, that is what the ReadXml method does, it automatically reads the schema and created the tables/fields and load the data. As untyped the objects are only available from such general properties like the Tables collection and Rows collection.

I have already added the xsd into the project, can I create tables from that spec?

I'm guessing you mean table objects/instances. Yes, simplest is to add to a form the DataSet component from Toolbox, in following dialog select Typed and select the schema you added. Now there is for example a "NewDataset1" object in form that represent the strongly typed dataset, so this already contains the schema tables/fields, calling ReadXml from this will load the data in file into it. For example:

Me.NewDataset1.ReadXml("file.xml")

By the way, if you doubleclick the xsd in project you get the dataset designer where you can review or edit the dataset layout, these are all available as class objects with properties from the typed dataset.

 

[ballybeg]

Ok great....fantastic so far thanks for getting me in the right direction!

So far I am all good, I have followed what you suggested and used the xsd spec to define a data set and that generated all the table objects that I need.

I have pulled the data into the data store and all the tables display on the form via a data grid.

Last question is ( and I know it is a naive one) The dataset and tables store the data in memory, and I need them to persist in a database on the local disk. How do I replicate the all the tables to a back end database and store the data there?

ballybeg

 

[JohnH]

I would try searching 'import xsd (or xml) to sql server' for example. The previous steps in generating and adding the xsd to project is a bit backwards in this regard, because once you have the schema in database and connect to it from VS the dataset and queries are automatically generated from that, which again could be used to ReadXml from the data file and push to db.