Having trouble exporting XML from a DataTable object then importing it into Excel by using File->Open there. The main problems are Excel refuses to recognize the schema if I write it. And if I don't write the schema, Excel doesn't 'invent' the correct one. First, some code:
DataTable dt;
DataRow dr;
dt = new DataTable("Employee", "xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance");
dt.Columns.Add("First Name", typeof(string));
dt.Columns.Add("Last Name", typeof(string));
dt.Columns.Add("Job Title", typeof(string));
dt.Columns.Add("Annual Salary", typeof(int));
dt.Columns.Add("Hourly Wage", typeof(int));
dt.Columns.Add("Termination Date", typeof(DateTime));
dt.Columns["Termination Date"].DateTimeMode = DataSetDateTime.Unspecified;
dt.Columns.Add("Hire Date", typeof(DateTime));
dt.Columns["Hire Date"].DateTimeMode = DataSetDateTime.Unspecified;
dr = dt.NewRow();
dr[0] = "Joe";
dr[1] = "Blow";
dr[2] = "Grunt";
dr[4] = 15;
dr[6] = new DateTime(1998, 3, 17);
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = "Jane";
dr[1] = "Doe";
dr[2] = "Manager";
dr[3] = 75000;
dr[5] = new DateTime(2015, 6, 26);
dr[6] = new DateTime(2014, 2, 12);
dt.Rows.Add(dr);
dt.WriteXml(@"C:\Users\Public\DataTable1.xml", XmlWriteMode.IgnoreSchema);
dt.WriteXml(@"C:\Users\Public\DataTable2.xml", XmlWriteMode.WriteSchema);
dt.WriteXmlSchema(@"C:\Users\Public\DataTable.xsd");
Notice the order of the columns I have added to my table and notice my data is specifically constructed to have null values in some cells in a very specific order. Now some output:
DataTable1.xml
<?xml version="1.0" standalone="yes"?>
<DocumentElement xmlns="xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance">
<Employee>
<First_x0020_Name>Joe</First_x0020_Name>
<Last_x0020_Name>Blow</Last_x0020_Name>
<Job_x0020_Title>Grunt</Job_x0020_Title>
<Hourly_x0020_Wage>15</Hourly_x0020_Wage>
<Hire_x0020_Date>1998-03-17T00:00:00</Hire_x0020_Date>
</Employee>
<Employee>
<First_x0020_Name>Jane</First_x0020_Name>
<Last_x0020_Name>Doe</Last_x0020_Name>
<Job_x0020_Title>Manager</Job_x0020_Title>
<Annual_x0020_Salary>75000</Annual_x0020_Salary>
<Termination_x0020_Date>2015-06-26T00:00:00</Termination_x0020_Date>
<Hire_x0020_Date>2014-02-12T00:00:00</Hire_x0020_Date>
</Employee>
</DocumentElement>
DataTable2.xml
<?xml version="1.0" standalone="yes"?>
<NewDataSet xmlns="xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance">
<xs:schema id="NewDataSet" targetNamespace="xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance" xmlns:mstns="xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance" xmlns="xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="xmlns_x003A_xsi_x003D_http_x003A__x002F__x002F_www.w3.org_x002F_2001_x002F_XMLSchema-instance_x003A_Employee" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Employee">
<xs:complexType>
<xs:sequence>
<xs:element name="First_x0020_Name" type="xs:string" minOccurs="0" />
<xs:element name="Last_x0020_Name" type="xs:string" minOccurs="0" />
<xs:element name="Job_x0020_Title" type="xs:string" minOccurs="0" />
<xs:element name="Annual_x0020_Salary" type="xs:int" minOccurs="0" />
<xs:element name="Hourly_x0020_Wage" type="xs:int" minOccurs="0" />
<xs:element name="Termination_x0020_Date" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0" />
<xs:element name="Hire_x0020_Date" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<Employee>
<First_x0020_Name>Joe</First_x0020_Name>
<Last_x0020_Name>Blow</Last_x0020_Name>
<Job_x0020_Title>Grunt</Job_x0020_Title>
<Hourly_x0020_Wage>15</Hourly_x0020_Wage>
<Hire_x0020_Date>1998-03-17T00:00:00</Hire_x0020_Date>
</Employee>
<Employee>
<First_x0020_Name>Jane</First_x0020_Name>
<Last_x0020_Name>Doe</Last_x0020_Name>
<Job_x0020_Title>Manager</Job_x0020_Title>
<Annual_x0020_Salary>75000</Annual_x0020_Salary>
<Termination_x0020_Date>2015-06-26T00:00:00</Termination_x0020_Date>
<Hire_x0020_Date>2014-02-12T00:00:00</Hire_x0020_Date>
</Employee>
</NewDataSet>
DataTable.xsd
<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" targetNamespace="xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance" xmlns:mstns="xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance" xmlns="xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="xmlns_x003A_xsi_x003D_http_x003A__x002F__x002F_www.w3.org_x002F_2001_x002F_XMLSchema-instance_x003A_Employee" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Employee">
<xs:complexType>
<xs:sequence>
<xs:element name="First_x0020_Name" type="xs:string" minOccurs="0" />
<xs:element name="Last_x0020_Name" type="xs:string" minOccurs="0" />
<xs:element name="Job_x0020_Title" type="xs:string" minOccurs="0" />
<xs:element name="Annual_x0020_Salary" type="xs:int" minOccurs="0" />
<xs:element name="Hourly_x0020_Wage" type="xs:int" minOccurs="0" />
<xs:element name="Termination_x0020_Date" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0" />
<xs:element name="Hire_x0020_Date" msdata:DateTimeMode="Unspecified" type="xs:dateTime" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
The WriteXML function apparently doesn't output properly formatted schema code (or Excel is too dumb to recognize it). So Excel claims the DataTable2.xml has no schema, but fear not I Excel the all powerful will simply invent one for you. It then goes on to, get this, import the schema as if it were data, then import the actual data in columns to the right of whatever columns are required to misrepresent the schema as data. That kind of brings me to the second problem. The WriteXML function 'brilliantly' leaves out any null valued members so as Excel (the all powerful, remember) 'invents' a schema it just starts reading elements and creating whatever columns it needs. It will reuse same-named columns if they already exist, but otherwise adds columns at the right. Since my first element contains null values for columns at index 3 and 5, those get left out until they are added by the second record. This effectively swaps the column order. That's not to mention the dumb 'ns1:' prefix appended to the column names if I have a namespace value in the DataTable constructor call. By the way, the 'DataSetDateTime.Unspecified' bit is used to prevent XML datetimes from coming out with the '2007-10-22T09:55:45-05:00' format that Excel can't parse back from text to datetime... which it is forced to do... without the schema... which it can't read. I tried putting in a default value (new DateTime()) for the columns, but this doesn't work because I'm using ImportRow from another table and I don't want the goofy '0001-1-1:00:00:00' default datetime default because, guess what, Excel also can't interpret that as a datetime. Although, it is able to interpret '1900-1-1:00:00:00' as a datetime and that's a default from T-SQL, I think. Any help will be greatly appreciated as I would like to get rid of my current workaround which is to stuff in a dummy first row with default values in all columns to force Excel to build the correct schema, then tell the users to delete it.
