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:
when we paste the output to a xml file
not fitting the xml format.
what will we do for that?
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.
The store procedure Sp_InsertData of this example
does not work.....Says in this line-->(@xmlString VARCHAR(MAX))
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.
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;
}
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.
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
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?
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();
Hi,
I want to provide download as XML format feature. How can I do this using the string XMLformat. Can you help?
Good Work Sandeep .. Its working perfect
Sundeep can you provide me the code for XML to datatable?
crating stored procedures
Hi Sandeep,
Excellent work !!
Can you please give us code to reverse this procedure , I means to convert xml to datatable.
Hi Sandeep ,
Can you please give us the code to do exact opposite , means to convert xml to datatable again?
Thanks in advanced
how to convert XML to data table?
TreeView control databinding to XML in C#
data binding treeview to XML file
thank you
Post a Comment