Force XML Tag to appear when serializing a dataset.

Question

I'm building a webservice which has a webmethod returning a Dataset object.

I'm having a problem XML tags are not written when its associated value is null.

A simplified example would be as follow:

ID   Name    Text
--  ------   -------------
1    NULL     test1              <------- null value
2    toto     test2
3    tata     test3

gets XML-serialized to:

<table roworder="1">
   <id>   1     </id>            <----- element missing when value = null
   <text> test1 </text>
</table>
<table roworder="2">
     <id>   2   </id>
   <name> toto  </name>
   <text> test2 </text>
</table>    
<table roworder="3">
     <id> 3     </id>
   <name> tata  </name>
   <text> test3 </text>
</table>

This is giving me problems when I hook this XML up in Reporting Services. If an element of the first row is null it won't recognize it as a report field and does the whole column is missing from the results.

Is there a way to force the XML serialization to put in empty elements if its associated value is null?

EX:

<table roworder="1">
   <id>   1     </id>
   <name>       </name>             <---- empty element for null value
   <text> test1 </text>
</table>

Thanks

Answer 1

You could change the SQL to return empty string instead of null.

select COALESCE(Name, '') from table_name;

[Edit] If that is not an option, there could be another (rather ugly) way.

1. Create another blank dataset with the schema extracted from the original dataset.
2. Iterate thru nullable columns in this dataset and set a DefaultValue.
3. Load the XML generated from first dataset into this dataset (just data, not schema again).
4. Generate XML from the second dataset.

ugh!

[Edit 2] Or you could just iterate through the Dataset and create XML on your own.

Answer 2

Chetan:

This is not an option as dataset we are returning comes from some weird OLEDB connection and we have no actual influence on the queries it makes to the DB.