Create XML from DataBase

In this post we will see how to create a xml from a database using LINQ.

Scenario:  Our goal is to create a xml from a table and save it in the local hard drive for further use using XDocument and XElement class.

Solution:  

Step 1:  Create a console application.

Step 2: Declare the connection string :

string connectionString ="Data Source=your datasource;Initial Catalog=catalog name;uid=userid;pwd=password";

SqlConnection mySqlConnection =new SqlConnection(connectionString);

Step 3: Write the query :

string selectString ="SELECT ACCOUNT_BUSINESS_UNIT,ACCOUNT_NAME,GENERAL_MANAGER,OFFSHORE_OPERATIONS_MANAGER FROM [Database].[Table] GROUP BY ACCOUNT_BUSINESS_UNIT,ACCOUNT_NAME,GENERAL_MANAGER,OFFSHORE_OPERATIONS_MANAGER";

Step 4: Create an arbitrary first value for the XDocument class. This is an example of using XElement :

XElement newContent = new XElement("AccountInformations",
                                    new XElement("Account",
                                        new XElement("AccountBusinessUnit", "A"),
                                        new XElement("AccountName", "B"),
                                        new XElement("GeneralManager", "C"),
                                        new XElement("OffshoreOperationsManager", "D")));

This will create XML Nodes in this format:

<AccountInformations>
<Account>
<AccountBusinessUnit>A</AccountBusinessUnit>
<AccountName>B</AccountName>
<GeneralManager>C</GeneralManager>
<OffshoreOperationsManager>D</OffshoreOperationsManager>
</Account>
</AccountInformations>

Step 5:

Now we have to create the xml using the XDocument :

XDocument accountInformation = new XDocument(new XDeclaration("1.0", "utf-8", "yes"), new XComment("XML Generated from Database"), newContent);

This will create XML tag :

<?xml  version="1.0" encoding="utf-8" ?> along with a XML comment.

Step 6:

Use ADO.NET to get data from the db and store the data in the SqlDataReader object.

Step 7:

Now read the DataReader object and inside the while loop generate the XML nodes dynamically. Hope you can read out the following statements:

while (rs.Read())
{
accountInformation.Element("AccountInformations").Add(new XElement("Account",new XElement("AccountBusinessUnit",rs.GetValue(0).ToString()),new XElement("AccountName",rs.GetValue(1).ToString()),new XElement("GeneralManager",rs.GetValue(2).ToString()),new XElement("OffshoreOperationsManager",rs.GetValue(3).ToString())));
}

It is following the same structure described earlier while creating the arbitrary XML Node.

Step 8:

Now remove the arbitrary node we created earlier. It's little bad stuff but I have not found any other solution.

accountInformation.Element("AccountInformations").Element("Account").Remove();

Step 9:

Now Save your data

accountInformation.Save("C:\\AccountInformations.xml");

That's it. You are done. Hope this will help you in real scenario. Enjoy coding.