Sunday, January 18, 2009

How to convert DataTable to XML in C#?

Following code illustrates about converting a DataTable to XML format. This is often required when passing a DataTable to a stored procedure. We can pass an XML directly to the procedure and process it.


/// <summary>
/// This method is used to convert the DataTable into string XML format.
/// </summary>
/// <param name="dtBuildSQL">DataTable to be converted.</param>
/// <returns>(string) XML form of the DataTable.</returns>
private static string ConvertDataTableToXML(DataTable dtBuildSQL)
{
DataSet dsBuildSQL = new DataSet();
StringBuilder sbSQL;
StringWriter swSQL;
string XMLformat;

sbSQL = new StringBuilder();
swSQL = new StringWriter(sbSQL);
dsBuildSQL.Merge(dtBuildSQL, true, MissingSchemaAction.AddWithKey);
dsBuildSQL.Tables[0].TableName = "Table";
foreach (DataColumn col in dsBuildSQL.Tables[0].Columns)
{
col.ColumnMapping = MappingType.Attribute;
}
dsBuildSQL.WriteXml(swSQL, XmlWriteMode.WriteSchema);
XMLformat = sbSQL.ToString();
return XMLformat;
}

Your comments are welcome!

19 comments:

Anonymous said...

when we paste the output to a xml file
not fitting the xml format.
what will we do for that?

Sandeep Aparajit said...

This function returns XML as string. "\r\n" characters might be present in the string. These are the carriage return characters in Windows. Just do a string.Replace("\r\n",String.Empty) on the final XML string and it should form a valid XML.

Please let me know if this helps you.

Anonymous said...

The store procedure Sp_InsertData of this example
does not work.....Says in this line-->(@xmlString VARCHAR(MAX))

Sandeep Aparajit said...

This might happen if your XML string goes beyond the VARCHAR(MAX) limit. To overcome this issue you can use (@xmlString XML) XML data type of sql. Which will allow a very long string as XML.
Hope this helps you.

Anonymous said...

Please correct my code..... I m not getting the correct data in treeview


protected void Page_Load(object sender, EventArgs e)
{
try
{
DataTable dtCategory = Class.Admin.DataProvider.GetCategory();


if (dtCategory.Rows.Count > 0)
{
XmlDataSource xml = new XmlDataSource();
xml.Data= ConvertDataTableToXML(dtCategory).ToString();
TreeViewCategory.DataSource = xml; // ConvertDataTableToXML(dtCategory);
TreeViewCategory.DataBind();
}
}
catch (Exception ex)
{
throw ex;
}
}
private static string ConvertDataTableToXML(DataTable dtBuildSQL)
{
DataSet dsBuildSQL = new DataSet();

StringBuilder sbSQL;
StringWriter swSQL;
string XMLformat;
sbSQL = new StringBuilder();
swSQL = new StringWriter(sbSQL);
dsBuildSQL.Merge(dtBuildSQL);//, true, MissingSchemaAction.AddWithKey);
dsBuildSQL.Tables[0].TableName = dtBuildSQL.ToString();
foreach (DataColumn col in dsBuildSQL.Tables[0].Columns)
{

col.ColumnMapping = MappingType.Attribute;

}
dsBuildSQL.WriteXml(swSQL, XmlWriteMode.WriteSchema);
XMLformat = sbSQL.ToString();
return XMLformat;
}

Sandeep Aparajit said...

Can you attach the snap show of data that you are getting in the TreeView?
I suspect that the generated XML contains '\r\n' characters, due to which you might not be getting the tree proper.

Anonymous said...

i have a treeview with one level childmenu's. by default these childmenu's are collapsed (set by ExpandDepth=0) but when a childnode is clicked the relative page is opened, but the treeview collapses all child menu's . now i want to expand the child node of which I have is clicked.

Please give me the solution

Ryan said...

Thanks this acticle was informative.
Including the namespaces helps us rookies out

using System.Text;
using System.IO;

did you use the same routine to retrieve XML from database and load back into a gridview?

pradeep said...

Hi..this is to that guy who faced problem with treeview,we cannot assign a string to a datasource directly. check this

XmlDataSource xmlDSObj = new XmlDataSource();
XmlDocument xmlDocObj = new XmlDocument();
xmlDocObj.LoadXml(ConvertDataTableToXML());
xmlDSObj.Data = xmlDocObj.InnerXml;
xmlDSObj.DataBind();

Jaishankar said...

Hi,
I want to provide download as XML format feature. How can I do this using the string XMLformat. Can you help?

Sanam from Karachi Pakistan said...

Good Work Sandeep .. Its working perfect

sanam from karachi pakistan said...

Sundeep can you provide me the code for XML to datatable?

Anonymous said...

crating stored procedures

About me said...

Hi Sandeep,
Excellent work !!
Can you please give us code to reverse this procedure , I means to convert xml to datatable.

About me said...

Hi Sandeep ,
Can you please give us the code to do exact opposite , means to convert xml to datatable again?
Thanks in advanced

Unknown said...

how to convert XML to data table?

Anonymous said...

TreeView control databinding to XML in C#

Kettic said...

data binding treeview to XML file

Hitesh said...

thank you

Post a Comment