[SOLVED] Read XML into DataTable. Need to match Schema

Issue

I am trying to read an XML file into a DataTable and I am having a hard time matching the schema, and converting it properly. This is what the XML could look like…

<?xml version="1.0" encoding="utf-8"?>
<School>
  <Teacher>
    <Name>Teacher1</Name>
    <Student>
      <Student-Name>Student1</Student-Name>
      <Gender>M</Gender>
      <Address>Address1</Address>
    </Student>
    <Student>
      <Student-Name>Student2</Student-Name>
      <Gender>F</Gender>
      <Address>Address2</Address>
    </Student>
  </Teacher>
  <Teacher>
    <Name>Teacher2</Name>
    <Student>
      <Student-Name>Student3</Student-Name>
      <Gender>F</Gender>
      <Address>Address3</Address>
    </Student>
  </Teacher>
</School>

I need to add it to a DataTable so that it the table looks like this

Name           Student-Name    Gender    Address
—————————————————-
Teacher1    Student1             M    Address1
Teacher1    Student2             F    Address2
Teacher2    Student3             F    Address3

DataTable.ReadXML() –> wont allow me to read in this XML file. The error says that DataTable doesn’t support schema inference
DataSet.ReadXML() –> doesn’t pick up on the schema and separates this into two tables the first being a Teacher Table and the second being a Student Table, even when it is set to Infer the schema.

The other problem is that I am doing this with several types of XML files. All of which have a different number of nodes nested within the student, and I need to make this generic so that it can read the XML regardless of the number of nodes.

I am wondering if I need to set up an XmlReader to read this in, or if there is any settings on the DataSet.ReadXML()/DataTable.ReadXML() that I need to use. Thank You.

Solution

If you are familiar with Xsd.exe (a tool in visual studio), you can generate Classes from the XML data which will allow you to load the data, in bulk, directly from the XML into the generated Classes.

Answered By – user1795804

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *