Setup:
I have a dataset that holds data that was retrieved from a database.  Some of the data in the dataset is null (for some, null for the whole column).  Upon successful retrieval of data, I want to write the data to an XML file.

Problem:
When I use the dataset.WriteXML(filename) method, it successfully writes the data to the xml file, BUT the columns that have null values throughout get left out of the final output (if there is a field that has a null value then it will not be represented in that record in the xml file).

Question:
Is there any way I can force the writing of the entire dataset (All columns being represented regardless if an entire column is null)?

Thanks in advance for your help.

I'm afraid you'll have to write code, the WriteXML method suppresses the null columns by design.

So you might do something like this:

XmlDocument xml = WriteDataTable(ds.Tables[0]);
xml.Save("XML File Path"); 

XmlDocument WriteDataTable(DataTable table)
{
  XmlDocument xml = new XmlDocument();
  xml.LoadXml("<Table/>");
  foreach ( DataRow dr in table.Rows)
  {
     XmlElement xmlRow = xml.CreateElement(table.TableName);
     foreach (DataColumn col in table.Columns)
    {
       XmlElement xmlCol = xml.CreateElement(col.ColumnName);
       xmlCol.InnerText = dr[col].ToString();
       xmlRow.AppendChild(xmlCol);
    }
  xml.AppendChild(xmlRow);
  }
  return xml;
}

Using XmlDocument totally bypasses the DataSet which is a much richer API.

The only reason you may need this is human readability, otherwise it is just extra markup, and if networked, extra traffic. An xml editor like XmlSpy http://www.altova.com/download_spy_enterprise.html or the Xml Data Editor in the IDE show the data tabulated with the nulls.

If you do use XmlDocument you might want to use one of the overloads that formats your output.
You can do a little hack to see the empty rows.

<TestDataSet>
      <DataTable1 AttColumn1="Value 1" AttColumn2="Value 2" />
      <DataTable1 />
      <DataTable1 />
</TestDataSet>

1. The schema element for DataTable must not be unique.
2. Convert the column elements to attributes in the schema. The IDE by default sets them as elements.
3. watch out for PKs, which wouldn't give you empty rows anyway.
4. If you are already doing it in the db, you might want to loosen up validation for the columns msprop:nullValue="_throw"|"_empty"|"_null"

HTH!

 

Something else I thought of.

You can leave all the columns as elements(default) but mark BOTH the Table and the columns as nillable.

<xs:element name="DataTable1"  nillable="true">
   <xs:complexType>
      <xs:sequence>
         <xs:element name="Column1" type="xs:string" minOccurs="0" nillable="true" />
         <xs:element name="Column2" type="xs:string" minOccurs="0" nillable="true" />
      </xs:sequence>
   </xs:complexType>
</xs:element>

Then you will end up getting xsi:nil="true" attribute in tags of the empty elements in the instance DataSet Xml

It is even bindable.

http://msdn2.microsoft.com/en-us/library/ybce7f69

HTH!

This post is very old but for what it is worth I would have liked to find this solution out there when I was searching so here it is: 

I had a similar problem where I created a disk cache solution using datasets, I wrote the files using DataSet.WriteXML(filePath) and read them back in via DataSet.ReadXML(filePath).  Exceptions were thrown if I tried to access a column from the dataset that was null. To get around this problem simply include the Schema when you read and write so that when you access the data the dataset object understands that the column exists but contains no data.

There are 2 ways of doing this depending upon your approach:
1) If you are using the same output for each call it is probably more efficient to reuse schemas:

MyDataSet.WriteXML(file)
MyDataSet.WriteXMLSchema(fileUniqueToOutputStructure)

MyDataSet.ReadXMLSchema(fileUniqueToOutputStructure)
MyDataSet.ReadXML(file)

2) Or, with a little more overhead but easier to manage and write - just write the XML schema into each dataset output:

DataSet.WriteXml(file, XmlWriteMode.WriteSchema)

DataSet.ReadXml(file, XmlReadMode.ReadSchema)

Thanks benhobgood,

  Thanks for the help, i have been search on this from the past few days.