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!
January 22, 2009 at 1:34 pm |
when we paste the output to a xml file
not fitting the xml format.
what will we do for that?
January 22, 2009 at 6:34 pm |
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.
February 11, 2009 at 1:48 pm |
The store procedure Sp_InsertData of this example
does not work…..Says in this line–>(@xmlString VARCHAR(MAX))
February 12, 2009 at 5:09 pm |
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.