Reading an XML file into a DataSet (ADO.NET)


When you import an XML file into a data set (DataSet.ReadXML) the data set will contain tables corresponding to all the element sections in the XML file which contains other elements.

Besides those though, the data set contains a bunch of relations which will allow you to query child rows for a given row in a table.
Let’s consider the following example of an XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<main>
<parentelement name="PE1">
    <childelement name="CE10"/>
    <childelement name="CE11"/>
</parentelement>
<parentelement name="PE2">
    <childelement name="CE20"/>
    <childelement name="CE21"/>
</parentelement>
</main>

When the above file is read into a dataset:
Dim testDataSet as New DataSet()
testDataSet.ReadXML(“c:\temp\test.xml”)

the data set that will be created will contain two tables (DataTable):
parentelement; fields: parentelement_id and name
childelement; fields: childelement_id and name

and a relation:
parentelement_childelement

The import process adds automatically a MappingType to each of the DataColumn columns.
The purpose for the mapping types the documentation says, is to instruct the DataSet or DataTable.WriteXml method how to create the xml file.

– If mapping type for a column is MappingType.Attribute the values for that column will be written in the XML as attributes, meaning exactly as in our test xml file
Of course by default, for our sample file when imported the DataColumn columns of the tables will have a mapping of MappingType.Attribute.

– If we change the mapping of each column to let’s say MappingType.Element when written back to disk the file will look like this:
<?xml version="1.0" standalone="yes"?>
<main>
  <parentelement>
    <parentelement_Id>0</parentelement_Id>
    <name>PE1</name>
    <childelement>
      <name>CE10</name>
      <parentelement_Id>0</parentelement_Id>
    </childelement>
    <childelement>
      <name>CE11</name>
      <parentelement_Id>0</parentelement_Id>
    </childelement>
  </parentelement>
  <parentelement>
    <parentelement_Id>1</parentelement_Id>
    <name>PE2</name>
    <childelement>
      <name>CE20</name>
      <parentelement_Id>1</parentelement_Id>
    </childelement>
    <childelement>
      <name>CE21</name>
      <parentelement_Id>1</parentelement_Id>
    </childelement>
  </parentelement>
</main>

If we would import this file back into the data set we will end up with the same two tables: parentelement and childelement and a parentelement_childelement relation.
Even though we have other elements: name, parentelement_id, childelement_id the ReadXML method will not create corresponding tables for (I assume) it is smart enough to consider that an element without children elements must be a value.

The DataColumns containing the two id fields: parentelement_id and childelement_id will be automatically assigned MappingType.Hidden.
The purpose of this is to avoid writing the values of these fields back to the XML file when WriteXML method is invoked. As you can see above, when we changed all the mappings of the columns to MappingType.Element (it could have been MappingType.Attribute as well), anything other than MappingType.Hidden the values of those fields are written back to the xml file which might not be the desired result.

One secondary effect of a column having a mapping of MappingType.Hidden is that when we display its DataTable in a DataGridView, that column will not be displayed.

Cheers.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s