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!


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. 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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: