如何将包含空Datetime字段的XML转换为DataSet？

Hey folks, let's work through how to convert an XML with empty DateTime fields into a DataSet while keeping the original XML structure intact. Based on the schema snippet you provided, here's a reliable approach I've used in similar scenarios:

核心思路 #

The main pain point here is that empty DateTime fields can trigger conversion errors when using the default DataSet.ReadXml() method—since it tries to map empty values to a non-nullable DateTime type. To fix this, we either need to explicitly mark DateTime fields as nullable in the XML schema or adjust the DataSet's column properties post-read, while ensuring the original XML structure is fully preserved.

1. 调整XML Schema支持空DateTime字段 #

First, update your inline schema to add the nillable="true" attribute to any DateTime fields that might be empty. For example, if you have a CREATE_DATE field, modify its element definition like this:

<xs:element minOccurs="0" name="CREATE_DATE" type="xs:dateTime" nillable="true"/>

This tells the DataSet that the field accepts null values, preventing format exceptions during conversion.

2. C#代码实现转换（完整示例） #

Since DataSet is a .NET framework component, here's a practical C# implementation that handles empty DateTime fields and retains your original XML structure:

using System.Data;
using System.IO;
using System.Xml;

public class XmlToDataSetHandler
{
    public DataSet ConvertXmlWithEmptyDateTimeFields(string xmlContent)
    {
        var dataSet = new DataSet();
        
        // Load the XML content with its inline schema
        using (var stringReader = new StringReader(xmlContent))
        using (var xmlReader = XmlReader.Create(stringReader))
        {
            // ReadSchema mode ensures the DataSet adopts the exact structure from your XML
            dataSet.ReadXml(xmlReader, XmlReadMode.ReadSchema);
        }

        // Fallback: Ensure all DateTime columns allow nulls (in case schema wasn't updated)
        foreach (DataTable table in dataSet.Tables)
        {
            foreach (DataColumn column in table.Columns)
            {
                if (column.DataType == typeof(DateTime))
                {
                    column.AllowDBNull = true;
                }
            }
        }

        return dataSet;
    }
}

3. 关键注意事项 #

• Preserve Inline Schema: Your XML includes an inline xs:schema—using XmlReadMode.ReadSchema ensures the DataSet's table/column structure matches this schema exactly.
• Handle Empty Values: If your XML has empty DateTime elements (like <CREATE_DATE></CREATE_DATE>) without the nillable attribute, the fallback loop in the code will force the column to accept nulls, avoiding conversion failures.
• Validate XML First: Always check your XML for syntax errors before conversion—invalid XML will cause ReadXml() to fail immediately.

4. Test with a Full XML Example #

Here's a test XML that includes an empty DateTime field, matching your schema structure:

<?xml version="1.0" encoding="UTF-8"?>
<NewDataSet>
  <xs:schema xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:xs="http://www.w3.org/2001/XMLSchema" id="NewDataSet">
    <xs:element msdata:IsDataSet="true" msdata:UseCurrentLocale="true" name="NewDataSet">
      <xs:complexType>
        <xs:choice maxOccurs="unbounded" minOccurs="0">
          <xs:element name="Table1">
            <xs:complexType>
              <xs:sequence>
                <xs:element minOccurs="0" name="CODE" type="xs:string"/>
                <xs:element minOccurs="0" name="CREATE_DATE" type="xs:dateTime" nillable="true"/>
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <Table1>
    <CODE>001</CODE>
    <CREATE_DATE></CREATE_DATE>
  </Table1>
  <Table1>
    <CODE>002</CODE>
    <CREATE_DATE>2024-05-20T14:45:00</CREATE_DATE>
  </Table1>
</NewDataSet>

After conversion, the resulting DataSet will have a Table1 table with two rows: the first row's CREATE_DATE will be DBNull.Value, and the second will hold the valid DateTime value—all while keeping your original XML structure intact.

内容的提问来源于stack exchange，提问作者jimbo R