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!

Advertisements

4 Responses to “How to convert DataTable to XML in C#?”

  1. Anonymous Says:

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

  2. Sandeep Aparajit Says:

    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.

  3. goncalohp@gmail.com Says:

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

  4. Sandeep Aparajit Says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: