XML is very versatile and still the most commonly used data format in most applications we developed today. The .NET Framework provides several namespaces and classes that allow developers to read, write and interact with the XML documents. Many developers over the last few months have requested me to write a tutorial on generating XML documents from the data stored in SQL Server database so I thought to write something on this topic. In this tutorial, I will demonstrate how we can load data from SQL Server database and generate complex XML documents programmatically using the classes and XML related attributes available in .NET Framework. I will also explain how we can create XML elements with multiple namespace prefixes.

Let’s start by creating a new Windows Form application GenerateComplexXMLFromDatabase in Visual Studio 2015 Professional Edition. I am using .NET Framework 4.6.1 for this tutorial but you can use an older version as well.

When the Windows Form designer appears in Visual Studio, add a TextBox control from the toolbox. We need to set following properties of TextBox control.

Dock = Fill

Font = Courier New, 12pt

Multiline = True

ScrollBars = Vertical

Textbox should look like a notepad style text editor after setting the above properties. We will use this textbox to display the generated XML later in this tutorial.

To load data from SQL Server database, I have created a new database and added two tables Roles and Users. I also added some sample data in these tables.

Double click the form in Visual Studio form designer to open the Form Load event handler. Write the following lines of code that is calling three different methods we will implement shortly.

The first method GetDataFromDatabase will load data from database and returns DataSet object.

Second method BuildObjects will take DataSet as input parameter and will build .NET classes from the DataSet. It will then return the Account object which will be the root element of our generated XML.

Third method GenerateXml will serialize the Account object and its nested classes into XML.

Loading Data from SQL Server

First of all, we need to write a code to load data from SQL Server Database. There are many different ways you can connect .NET applications with SQL Server database. You can use ADO.NETDataReader and DataSet objects or you can use Entity Framework to communicate with database. For the purpose of this tutorial, I will write very basic and straight forward ADO.NET code that will load both Users and Roles tables into DataSet object. The following code is creating two SqlDataAdaper objects which will load data from Roles and Users database tables. The code is also creating Relationship between those two tables within DataSet. This relationship will be used later in this tutorial when we will iterate both parent and child tables to generate XML. If you want to read more about ADO.NET then read my other tutorials on ADO.NET technology.

Creating .NET Classes with XML Serialization Attributes

We want to generate XML similar to the following so we need to create few classes and decorate them with XML attributes.

First of all, create a User class and decorate the class with Serializable and XMLRoot attributes. The Serializable attribute indicates that a class can be serialized and XMLRoot attribute controls XML serialization of the attribute target as an XML root element. The properties of the class User are decorated with XmlElement attribute which indicates that a public field or property will be serialized as an XML element.

If we will serialize the above User class, the following XML will be generated. Notice how the FacebookId and TwitterId elements are generated with a facebook and twitter prefixes. This is because the properties FacebookId and TwitterId of User class are decorated with an XmlElement that specified the namespaces as well. These namespaces will be matched with the similar namespaces we will define shortly in XML generation code.

Next we need to create following Role class in the project. Notice that the Id property is decorated with the XmlAttribute attribute which specifies that the XmlSerializer must serialize the class member as an XML attribute. We want Description property of the Role class to generate CDATA contents so I am decorating the actual Description property with XmlIgnore attribute to instructs the XmlSerializer that we don’t want this property to be serialized at all and then create another property DescriptionCData which create CDATA section and return it as XmlCDataSection object.

Build Objects to Generate XML

Once all the above classes are implemented, we need to create objects of these classes and need to set properties of from the data loaded from the database into DataSet. Let’s write a second method BuildObjects in our project Form1 class that will take DataSet as an input parameter and will build Account as well as nested objects from it.

In the above code, the first foreach loop will iterate over all the Roles available in Roles table in DataSet. It will then create a Role class object for every database row and populate the properties of Role object with the data available in every row in Roles table. The method then calls GetChildRows method to fetch the child table User objects. Notice how the DataRelation object is passed as a parameter to GetChildRows method. This is to tell GetChildRows method to fetch data only from the child table which is related to parent table using the relation named Roles_Users. There may be some other child tables of parent table Roles in DataSet but they will not be related with the parent table Roles using the same relationship object and name so they will not be fetched here. If child rows are found, the second foreach loop will iterate over the child rows and will create User class objects. Finally, users will be added in the UsersContainer and roles will be added in RolesContainer.

Build Objects to Generate XML

Once we have our Account object filled with roles and users, we need to use .NET Framework XmlSerializer to serialize the object into XML. Following method GenerateXml will do exactly that. The method will also create XmlSerializerNamespaces object to add three namespaces with different prefixes. Notice that the facebook and twitter namespaces URLs are matching with the Namespaces we used above to decorate the User class properties FacebookId and TwitterId. Finally, we are using Serialize method of XmlSerializer class to generate the XML from .NET objects.

Press F5 to run the project and you will see the XML similar to the following generated in the TextBox control.

In this tutorial, I tried my best to teach you how we can load data from SQL Server database and generate complex XML documents programmatically using C#. If you are struggling with the tutorial code then you can download the complete project source code by clicking the Download Code button on top of this tutorial. Keep visiting my website for more tutorials in the future.