C# – Working with interlaced XML database

Today I read a thread on mycsharp.de asking about how man can work with a database whose type is a interlace XML. The XML file has following structure

<?xml version="1.0"  encoding="utf-8"?>
<xs:schema id="DatenbankSpiel" targetNamespace="http://Weltkarte/DantenbankAll" elementFormDefault="qualified" xmlns="http://Weltkarte/DantenbankAll.xsd" xmlns:mstns="http://Weltkarte/DantenbankAll.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Staedte">
    <xs:complexType>
		<xs:sequence>
			<xs:element name="Name" type="xs:string"/>
			<xs:element name="Fraktion" type="xs:int"/>
			<xs:element name="Position-X" type="xs:int"/>
			<xs:element name="Position-Y" type="xs:int"/>
			<xs:element name="Verteidigung-Anzahl">
				<xs:complexType>
				  <xs:sequence>
					<xs:element name="Anzahl" />
					<xs:element name="Typ" />
				  </xs:sequence>
				</xs:complexType>
			</xs:element>
		</xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

As you can see that there is a child table “Verteidigung-Anzahl” in the table “Staedte”. So the question is how one works correctly with this structure, for example if one wants to insert a row to table “Staedte”. How can we insert data in child-table row? The problem is pretty complicated if we look the database at XML format because we always think of row-object but it will be more clear if we consider them as database with foreign key. That means the table “Verteidigung-Anzahl” will be linked to “Staedte” through a defined foreign key.
Let’s see the DataSet with DataSet Virtualizer

You can recognize that there is an automatic generated foreign key “Staedte_Id” in table “Staedte”. This foreign key is used to describe the relation between 2 tables. And the code snippet below demonstrates how we can insert a new row correctly to this type of structure.

static void Main(string[] args)
{
	DataSet dsDatabase = new DataSet();
	dsDatabase.ReadXmlSchema("test.xml");
	DataRow dtRow = dsDatabase.Tables[0].NewRow();
	dtRow["Name"] = "rongchaua";
	dtRow["Fraktion"] = 1;
	dtRow["Position-X"] = 1;
	dtRow["Position-Y"] = 1;
	dsDatabase.Tables[0].Rows.Add(dtRow);
	int nStaedteId = Convert.ToInt32(dtRow["Staedte_Id"]);
	dtRow = dsDatabase.Tables["Verteidigung-Anzahl"].NewRow();
	dtRow["Anzahl"] = 1;
	dtRow["Typ"] = "Kanone";
	dtRow["Staedte_Id"] = nStaedteId;
	dsDatabase.Tables["Verteidigung-Anzahl"].Rows.Add(dtRow);
	Console.WriteLine("Write successfully");
	Console.ReadLine();
}

After reading and editing the database, just call WriteXmlSchema if you want to save changes back to XML file. The complete source code you can download here. “Working with interlaced XML

UPDATE 09.10.2010
For reading from serialized XML we can use a XMLSerializer to read data from XML. For example, we have a XML with following format:

<?xml version="1.0" ?>
<ArrayOfMessdatensatz xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<Messdatensatz>
		<Zeitpunkt>2008-01-01T00:00:00</Zeitpunkt>
		<Standort>
			<x>13.34</x>
			<y>99</y>
		</Standort>
		<Sensor1>0</Sensor1>
		<Sensor2>0</Sensor2>
	</Messdatensatz>
	<Messdatensatz>
		<Zeitpunkt>2008-01-01T00:02:37.5</Zeitpunkt>
		<Standort>
			<x>31.54</x>
			<y>43</y>
		</Standort>
		<Sensor1>0</Sensor1>
		<Sensor2>0</Sensor2>
	</Messdatensatz>
	<Messdatensatz>
		<Zeitpunkt>2008-01-01T00:05:15</Zeitpunkt>
		<Standort>
			<x>52</x>
			<y>38</y>
		</Standort>
		<Sensor1>0</Sensor1>
		<Sensor2>0</Sensor2>
	</Messdatensatz>
	<Messdatensatz>
		<Zeitpunkt>2008-01-01T00:07:52.5</Zeitpunkt>
		<Standort>
			<x>72.81</x>
			<y>28</y>
		</Standort>
		<Sensor1>0</Sensor1>
		<Sensor2>0</Sensor2>
	</Messdatensatz>
</ArrayOfMessdatensatz>

We define a class Messdatensatz with appropriated Properties.

public class Messdatensatz
{
	public DateTime Zeitpunkt { get; set; }
	public Point Standort { get; set; }
	public int Sensor1 { get; set; }
	public int Sensor2 { get; set; }
}

public class Point
{
	public double x { get; set; }
	public double y { get; set; }
}

Then deserialize from object

List<Messdatensatz> lstMDS = new List<Messdatensatz>();
XmlSerializer serializer = new XmlSerializer(typeof(List<Messdatensatz>));
FileStream fs = new FileStream(@"..\..\test2.xml", FileMode.Open);
lstMDS = (List<Messdatensatz>)serializer.Deserialize(fs);

3 thoughts on “C# – Working with interlaced XML database”

  1. Thanks for sharing this.

    (Nitpicking: The German word “man”, as used in the passive, is translated to “one” in English; as in “So the question is how ONE works correctly with this structure”)

Leave a Reply

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