DataSet.GetXml () does not return xml for null or empty columns

Question

When I call dataSet.GetXml (), I do not get any xml returned for columns with null or empty values. Is there a simple and effective way around this? Example problem below. Note that a2 is missing from the second section of the results.

<results> <a1>test1</a1> <a2>test2</a2> <a3>test3</a3> </results> <results> <a1>Atest1</a1> <a3>Atest3</a3> </results> 

Answer 1

The problem is described in detail in this Microsoft KB article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q317961 . See the previous SO question for more details: DataSet.GetXml does not return null results .

I do not think there is a good solution to your direct question. In this context, there may be another way to approach the problem.

Answer 2

One solution that worked for me.

First, clone the DataTable, make all the columns of the type string, replace all null values ​​with string.empty , then call GetXml in the new DataSet.

  DataTable dtCloned = dt.Clone(); foreach (DataColumn dc in dtCloned.Columns) dc.DataType = typeof(string); foreach (DataRow row in dt.Rows) { dtCloned.ImportRow(row); } foreach (DataRow row in dtCloned.Rows) { for (int i = 0; i < dtCloned.Columns.Count; i++) { dtCloned.Columns[i].ReadOnly = false; if (string.IsNullOrEmpty(row[i].ToString())) row[i] = string.Empty; } } DataSet ds = new DataSet(); ds.Tables.Add(dtCloned); string xml = ds.GetXml(); 

Answer 3

I searched the whole world to solve writing empty fields in XML using DataSet.WriteXML (). I found that the following works are optimized for performance. I created a function for your convenience. Modify the data tables one by one 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; } 

Answer 4

  if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count == 0) { foreach (DataTable dt in ds.Tables) { foreach (DataColumn dc in dt.Columns) { dc.DataType = typeof(String); } } DataRow dr = ds.Tables[0].NewRow(); for (int i = 0; i < dr.ItemArray.Count(); i++) { dr[i] = string.Empty; } ds.Tables[0].Rows.Add(dr); }