Question: I'm exporting a System.Data.DataTable to XML. So far it works fine. But I want to have all the data in attributes, which works fine as well. But my problem now, if in one column, all rows are NULL, no empty attributes are written. So if I read the XML back to a DataTable, it lacks this column...
How can I force write all columns even when they are empty ?
(DataType not necessarely string)
public void ExportTable(string strDirectory, DataTable dtt)
{
using (System.Data.DataSet ds = new System.Data.DataSet()) {
string strTable = dtt.TableName;
ds.Tables.Add(dtt);
ds.DataSetName = strTable;
// Move data to attributes
foreach (DataTable dt in ds.Tables) {
foreach (DataColumn dc in dt.Columns) {
dc.ColumnMapping = MappingType.Attribute;
}
}
System.Xml.XmlWriterSettings settings = new System.Xml.XmlWriterSettings();
settings.Indent = true;
//settings.Encoding = System.Text.Encoding.GetEncoding("ISO-8859-1")
settings.Encoding = System.Text.Encoding.UTF8;
settings.CloseOutput = true;
settings.CheckCharacters = true;
settings.NewLineChars = "\r\n";
// vbCr & vbLf
// Write as UTF-8 with indentation
using (System.Xml.XmlWriter w = System.Xml.XmlWriter.Create(System.IO.Path.Combine(strDirectory, strTable + ".xml"), settings)) {
// Strip out timezone
foreach (DataTable dt in ds.Tables) {
foreach (DataColumn dc in dt.Columns) {
if (object.ReferenceEquals(dc.DataType, typeof(DateTime))) {
dc.DateTimeMode = DataSetDateTime.Unspecified;
}
}
}
ds.Tables[0].WriteXml(w, XmlWriteMode.IgnoreSchema);
w.Flush();
w.Close();
}
// w
}
// ds
}
// ExportTable
VB.NET original:
Public Sub ExportTable(strDirectory As String, dtt As DataTable)
Using ds As New System.Data.DataSet()
Dim strTable As String = dtt.TableName
ds.Tables.Add(dtt)
ds.DataSetName = strTable
' Move data to attributes
For Each dt As DataTable In ds.Tables
For Each dc As DataColumn In dt.Columns
dc.ColumnMapping = MappingType.Attribute
Next dc
Next dt
Dim settings As New System.Xml.XmlWriterSettings()
settings.Indent = True
'settings.Encoding = System.Text.Encoding.GetEncoding("ISO-8859-1")
settings.Encoding = System.Text.Encoding.UTF8
settings.CloseOutput = True
settings.CheckCharacters = True
settings.NewLineChars = vbCrLf ' vbCr & vbLf
' Write as UTF-8 with indentation
Using w As System.Xml.XmlWriter = System.Xml.XmlWriter.Create(System.IO.Path.Combine(strDirectory, strTable & ".xml"), settings)
' Strip out timezone
For Each dt As DataTable In ds.Tables
For Each dc As DataColumn In dt.Columns
If dc.DataType Is GetType(DateTime) Then
dc.DateTimeMode = DataSetDateTime.Unspecified
End If
Next dc
Next dt
ds.Tables(0).WriteXml(w, XmlWriteMode.IgnoreSchema)
w.Flush()
w.Close()
End Using ' w
End Using ' ds
End Sub ' ExportTable
DataTable. WriteXml method takes an XML file name and writes data to it. In the second method, wel use a SELECT statement with a FOR XML clause to select data from the database in XML format and then we use the ExecuteXmlReader method of the SqlCommand class to load XML in an XmlDocument object.
There are the following 3 ways to convert a DataTable to a List. Using a Loop. Using LINQ. Using a Generic Method.
Every XML attribute must be assigned a value that is enclosed in a pair of single or double quotation marks. There is no equivalent in plain text to denote a NULL value. A pair of quotation marks with no value to represent an empty string is not the same as a NULL value. Therefore, the only way to represent a NULL attribute is to omit the attribute.
This means that you will need to either set AllowDBNull
to false and assign a suitable DefaultValue
on the DataColumn, or include the schema.
Also, see Handling Null Values (ADO.NET)., particularly this section which explains the behavior:
In addition, the following rules apply for an instance of DataRow.["columnName"] null assignments:
1.The default default value is DbNull.Value for all except the strongly typed null columns where it is the appropriate strongly typed null value.
2.Null values are never written out during serialization to XML files (as in "xsi:nil").
3.All non-null values, including defaults, are always written out while serializing to XML. This is unlike XSD/XML semantics where a null value (xsi:nil) is explicit and the default value is implicit (if not present in XML, a validating parser can get it from an associated XSD schema). The opposite is true for a DataTable: a null value is implicit and the default value is explicit.
4.All missing column values for rows read from XML input are assigned NULL. Rows created using NewRow or similar methods are assigned the DataColumn's default value.
5.The IsNull method returns true for both DbNull.Value and INullable.Null.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With