Excel rejects XSD from Visual Studio DataSet Designer.

R

rico

Visual Studio 2008 SP1, .NET 3.5 SP1, Excel 2003 & Excel 2007

In Visual Studio within a C# project add a DataSet to the project. Within
the Toolbox there is only one possible choice if you are not using a database
- the DataTable, select this and add a column to the empty table.

Excel can read the XSD (and data) but it can not export data entered on the
spread sheet to an XML file, the map is not exportable because "a mapped
element's relationship with other elements cannot be preserved".

Here is the generated XSD:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="DataSet1" targetNamespace="http://tempuri.org/DataSet1.xsd"
xmlns:mstns="http://tempuri.org/DataSet1.xsd"
xmlns="http://tempuri.org/DataSet1.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:msprop="urn:schemas-microsoft-com:xml-msprop"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:annotation>
<xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource">
<DataSource DefaultConnectionIndex="0"
FunctionsComponentName="QueriesTableAdapter" Modifier="AutoLayout, AnsiClass,
Class, Public" SchemaSerializationMode="IncludeSchema"
xmlns="urn:schemas-microsoft-com:xml-msdatasource">
<Connections />
<Tables />
<Sources />
</DataSource>
</xs:appinfo>
</xs:annotation>
<xs:element name="DataSet1" msdata:IsDataSet="true"
msdata:UseCurrentLocale="true" msprop:Generator_DataSetName="DataSet1"
msprop:Generator_UserDSName="DataSet1"
msprop:EnableTableAdapterManager="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="DataTable1"
msprop:Generator_UserTableName="DataTable1"
msprop:Generator_RowDeletedName="DataTable1RowDeleted"
msprop:Generator_TableClassName="DataTable1DataTable"
msprop:Generator_RowChangedName="DataTable1RowChanged"
msprop:Generator_RowClassName="DataTable1Row"
msprop:Generator_RowChangingName="DataTable1RowChanging"
msprop:Generator_RowEvArgName="DataTable1RowChangeEvent"
msprop:Generator_RowEvHandlerName="DataTable1RowChangeEventHandler"
msprop:Generator_TablePropName="DataTable1"
msprop:Generator_TableVarName="tableDataTable1"
msprop:Generator_RowDeletingName="DataTable1RowDeleting">
<xs:complexType>
<xs:sequence>
<xs:element name="DataColumn1"
msprop:Generator_UserColumnName="DataColumn1"
msprop:Generator_ColumnPropNameInRow="DataColumn1"
msprop:Generator_ColumnVarNameInTable="columnDataColumn1"
msprop:Generator_ColumnPropNameInTable="DataColumn1Column" type="xs:string"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

If you modify it to be:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="DataSet1" targetNamespace="http://tempuri.org/DataSet1.xsd"
xmlns:mstns="http://tempuri.org/DataSet1.xsd"
xmlns="http://tempuri.org/DataSet1.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:msprop="urn:schemas-microsoft-com:xml-msprop"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:annotation>
<xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource">
<DataSource DefaultConnectionIndex="0"
FunctionsComponentName="QueriesTableAdapter"
GeneratorFunctionsComponentClassName="QueriesTableAdapter"
Modifier="AutoLayout, AnsiClass, Class, Public"
SchemaSerializationMode="IncludeSchema"
UserFunctionsComponentName="QueriesTableAdapter"
xmlns="urn:schemas-microsoft-com:xml-msdatasource">
<Connections />
<Tables />
<Sources />
</DataSource>
</xs:appinfo>
</xs:annotation>
<xs:element name="dataroot">
<xs:complexType>
<xs:sequence>
<xs:element ref="DataSet1" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
<xs:attribute name="generated" type="xs:dateTime" />
</xs:complexType>
</xs:element>
<xs:element name="DataSet1" msdata:IsDataSet="true"
msdata:UseCurrentLocale="true" msprop:Generator_DataSetName="DataSet1"
msprop:Generator_UserDSName="DataSet1"
msprop:EnableTableAdapterManager="true">
<!--
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="DataTable1"
msprop:Generator_UserTableName="DataTable1"
msprop:Generator_RowDeletedName="DataTable1RowDeleted"
msprop:Generator_TableClassName="DataTable1DataTable"
msprop:Generator_RowChangedName="DataTable1RowChanged"
msprop:Generator_RowClassName="DataTable1Row"
msprop:Generator_RowChangingName="DataTable1RowChanging"
msprop:Generator_RowEvArgName="DataTable1RowChangeEvent"
msprop:Generator_RowEvHandlerName="DataTable1RowChangeEventHandler"
msprop:Generator_TablePropName="DataTable1"
msprop:Generator_TableVarName="tableDataTable1"
msprop:Generator_RowDeletingName="DataTable1RowDeleting">
-->
<xs:complexType>
<xs:sequence>
<xs:element name="DataColumn1"
msprop:Generator_UserColumnName="DataColumn1"
msprop:Generator_ColumnPropNameInRow="DataColumn1"
msprop:Generator_ColumnVarNameInTable="columnDataColumn1"
msprop:Generator_ColumnPropNameInTable="DataColumn1Column" type="xs:string"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
<!--
</xs:element>
</xs:choice>
</xs:complexType>
-->
</xs:element>
</xs:schema>

The DataSet is now exportable, but the strongly typed class generated by the
designer no longer matches the XSD and so will not work.

How do you generate an XSD using the Visual Studio DataSet designer that is
usable by Excel?

I originaly posted this in:

MSDN Forums » Data Platform Development » XML and the .NET Framework but
have not had a reply.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Design of web service for Excel consumption 0
DiffGram whose associated schema is not supported. 1
export excell data as xml 0
How to the xsd:keyref field 0
XSD Schema in Word2003, why all possible elements listed at top le 1
prefix namespace as ns0 0
How to import XML child structures to Access 1
Keep schemaLocation attribute when saving to XML data from Excel 0

Top