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.