XML to Dataset

[dpk136]

The problem is that I have an XML file that needs to put data into 2 seperate tables one is testing and the other is unit. they need to link to each other, every unit has to have a testing records, testing can have more than one units.

How can I make it so this goes into the testing table as

number  |  UnitNum
82739   |    1

No unit number is given, but this must link back to the testing record. Please help. all the stuff i have is below. Thanks.


Sample XML

<testing>
  <number>82739</number>
  <unit>
    <price>83.74</price>
    <location>San Diego</location>
    <ready>Y</ready>
  </unit>
</testing>

Here is what I currently have (this works to pull in all the main parts of the records

protected void Page_Load(object sender, EventArgs e)
  {
    XmlReaderSettings settings = new XmlReaderSettings();

    //Change the settings in the XML Reader
    settings.IgnoreWhitespace = true;
    settings.IgnoreComments = true;

    //path of file
    string filePath = "C:\\Data\\";

    DataSet myDS = new DataSet();

    DataTable myTable = new DataTable("testing");

    myTable.Columns.Add("number", typeof(string));
    myTable.Columns.Add("UnitNum", typeof(int));


    //Add the table to the dataset
    myDS.Tables.Add(myTable);
            
    //combine the path and filename of the XML File
    string cartsFile = Path.Combine(filePath, "15recordsInXML.xml");

    using (XmlReader reader = XmlReader.Create(cartsFile, settings))
    {
      myDS.ReadXml(reader);

      GridView1.DataSource = myDS;
      GridView1.DataBind();
    }

  }

David Kuhn
------------------

http://www.davidpkuhn.com

 

[jmeckley]

where does the xml file come from, and can you modfiy how the file is create? If so I would output the xml as a dataset. then you can load the file directly into a dataset and bind to grid.

if not you will need to walk through the xml and manually create the tables/relations.

ultimately you want this correct?

Table: testing
---------------
testingId     | int, PK, auto number
testingNumber | long not null

Table: unit
---------------
unitId     | int, PK, auto number
price      | double
location   | string
ready      | bool
testingId  | int, FK to testing.testingId

your dataset would look like this

DataTable testing = new DataTable("testing");
testing.Columns.Add("testingId", typeof(int));
testing.Columns.Add("testingNumber", typeof(long));
testing.Columns[0].Seed = 0;
testing.Columns[0].AutoIncrement = true;
testing.PrimaryKey = new DataColumn[] { testing.Columns[0] };

DataTable unit = new DataTable("unit");
unit.Columns.Add("unitId", typeof(int));
unit.Columns.Add("price", typeof(double));
unit.Columns.Add("location", typeof(string));
unit.Columns.Add("ready", typeof(bool));
unit.Columns.Add("testingId", typeof(int));
unit.Columns[0].Seed = 0;
unit.Columns[0].AutoIncrement = true;
unit.PrimaryKey = new DataColumn[] { testing.Columns[0] };

DataSet ds = new DataSet();
ds.Tables.Add(testing);
ds.Tables.Add(unit);
ds.Relations.Add("UnitsToTesting", testing.Column[0], unit[4]);

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[dpk136]

I cannot modify how it is created. It comes from a customer.

Thanks for showing me how to autonumber...

So, you are saying to use the reader to go line by line and read in each one, placing it in the correct place in the dataset?

This sounds like a pain in the ass, but if it is the only way, then that is what i'll do.

What happens if the XML changes and gets a new field added? how do i account for that.

thanks for the quick response.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[jmeckley]

if the xml changes then you will need to update your code. the customer shouldn't change withour your approval or, at at a minimum, your knowledge.

it's no different then if the db schema changes. you would need to update your system to account for the changes.

because of the data format you will need to read the xml file node by node comparing the current testing node to the previous testing node. This will dermine if you need to add a new testing DataRow, or append the exsting testingNumber to the current unit DataRow.

Jason Meckley
Programmer
Specialty Bakers, Inc.