dataSet.GetXml() doesn't return xml for null or blank columns

Question

When I call dataSet.GetXml() I don't get any xml returned for columns with null or blank values. Is there a simple, efficient way to get around this? An example of the problem below. Notice how a2 is missing from the second results section.

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

Solution 1

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

I don't think there is a good solution to your direct question. Given context, there may be another way to approach the problem though.

Solution 2

One solution that worked for me.

First clone the DataTable, make all columns of type string, replace all null values with string.empty, then call GetXml on a 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();

Solution 3

I have been searching the whole world for a solution of writing null fields to XML using DataSet.WriteXML(). I found that following works in a 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;
    }

Comments

[Marc Gravell]

What value would you put in for a null? Perhaps consider giving the column a default value?

[Ben]

I would just have the value as blank. I was hoping that there was a way to get all of the xml schema info without having to explicitly set column default values, or do any sort of looping though the xml as I have to do this for many different result sets.

[John Saunders]

blank is not the same thing as null

[John Saunders]

possible duplicate of DataSet.GetXml not returning null results

[Matt Taylor]

You definitely need to add some explanation to this answer to tell us why you've added this to an already answered question

[Prateek Gupta]

One edit: If dtCloned.Rows is zero then we should add -> if (dtCloned.Rows.Count == 0) dtCloned.Rows.Add();