Dataset to xml Null Values

Question

I have the code below, where from 3 tables I take the data and write an xml.
I want write (when a record column has null value) the column on the xml with null value. For example if (Category_name == Null ) to write on the xml (Null) Right now the code skip the column and don’t even have this column on the xml.

 string xmlFileData = "";

    string[] tables = new string[] { "category",  "company", "config" };
    string query;
    xmlFileData += "<MyXml>";
    SqlConnection conn;
    dbconnect obj;
    obj = new dbconnect();//initailizing class object
    for (int i = 0; i < tables.Length; i++)
    {
        string ifemptquery;
        DataSet ds = new DataSet();

        DataSet ds1 = new DataSet();
        conn = obj.getConnection(); //calling connection function

        ifemptquery = "SELECT * FROM " + tables[i] ";
        SqlCommand cmd1 = new SqlCommand(ifemptquery, conn);
        conn.Open();
        SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
        DataTable dt1 = new DataTable();
        da1.Fill(dt1);
        conn.Close();
        if (dt1.Rows.Count > 0)
        {
            query = "SELECT * FROM " + tables[i] ";
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            conn.Close();
            conn.Dispose();
            ds.DataSetName = tables[i];
            string vartbname = tables[i];
            string trimed_tbname = vartbname.Replace("_", "");
            ds.Tables[0].TableName = trimed_tbname;
            xmlFileData += ds.GetXml();
        }
        else
        {

        }


    }
    xmlFileData += "</MyXml>";
    File.WriteAllText(Server.MapPath("~/xmlbackup/") + "Backup.xml", xmlFileData);

Answer 1

Refer similar question here - dataSet.GetXml() doesn't return xml for null or blank columns

Apart from solutions mentioned there, you can also traverse through dataset and write XML using XmlTextWriter. This method is not recommended if you are dealing with huge data.

Answer 2

I have been searching the whole world for a solution of writing null fields to XML using DataSet.WriteXML(). The answer posted by Vlad is the one I also used in my project but I found that following works in a much more performance optimized way. I have created a function for your convenience. Change your dataset tables one after the other by calling the following function and replacing the tables.

private DataTable GetNullFilledDataTableForXML(DataTable dtSource)
{
    // Create a target table with same structure as source and fields as strings
    // We can change the column datatype as long as there is no data loaded
    DataTable dtTarget = dtSource.Clone();
    foreach (DataColumn col in dtTarget.Columns)
        col.DataType = typeof(string);

    // Start importing the source into target by ItemArray copying which 
    // is found to be reasonably fast for nulk operations. VS 2015 is reporting
    // 500-525 milliseconds for loading 100,000 records x 10 columns 
    // after null conversion in every cell which may be usable in many
    // circumstances.
    // Machine config: i5 2nd Gen, 8 GB RAM, Windows 7 64bit, VS 2015 Update 1
    int colCountInTarget = dtTarget.Columns.Count;
    foreach (DataRow sourceRow in dtSource.Rows)
    {
        // Get a new row loaded with data from source row
        DataRow targetRow = dtTarget.NewRow();
        targetRow.ItemArray = sourceRow.ItemArray;

        // Update DBNull.Values to empty string in the new (target) row
        // We can safely assign empty string since the target table columns
        // are all of string type
        for (int ctr = 0; ctr < colCountInTarget; ctr++)
            if (targetRow[ctr] == DBNull.Value)
                targetRow[ctr] = String.Empty;

        // Now add the null filled row to target datatable
        dtTarget.Rows.Add(targetRow);
    }

    // Return the target datatable
    return dtTarget;
}