Excel 2010 XML Export creates empty element while minoccurs="0"

[XmlDev]

The exported XML always contains elements for columns mapped to XSD xs:element with minoccurs="0", while there is no data in the cells.
I would have expected Excel to not have created the elements at all.

More in detail:
An XML map is used to map the Excel columns. The XSD contains an xs:sequence of xs:element of type another complexType that is an xs:sequence itself.
The minoccurs="0" is set on both the xs:element in the main xs:sequence and on the xs:sequence in its complexType. In Excel the contents of the corresponding cells is cleared. In output it creates the element with all elements of its complexType, some of which are mandatory, of course without a value.
An example makes this less abstract:
XSD fragment:
<xs:element name="Root"></xs:element>
<xs:complextype></xs:complextype>
<xs:sequence></xs:sequence>
<xs:element name="Person" maxoccurs="unbounded"></xs:element>
<xs:complextype></xs:complextype>
<xs:sequence></xs:sequence>
<xs:element name="Name" type="xs:string" minoccurs="1" maxoccurs="1"></xs:element>
<xs:element name="MandatoryAddress" type="Address" minoccurs="1" maxoccurs="1"></xs:element>
<xs:element name="OptionalAddress1" type="Address" <strong="">minOccurs="0" maxOccurs="1"/></xs:element>
<xs:element name="OptionalAddress2" type="Address" <strong="">minOccurs="0" maxOccurs="1"/></xs:element>

The complexType Address is defined in the XSD as follows:
<xs:complextype name="Address"></xs:complextype>
<xs:sequence <strong="">minOccurs="0"></xs:sequence>
<xs:element name="StreetName" type="xs:string" <strong="">minOccurs="1" maxOccurs="1"/></xs:element>
<xs:element name="HouseNumber" type="xs:integer" minoccurs="0" maxoccurs="1"></xs:element>

This is mapped in the Excel sheet to corresponding columns: Name, StreetName, HouseNumber, StreetName1, HouseNumber1, StreetName2, HouseNumber2
If I don't put any data in the last 4 columns, Excel still exports the elements for it, empty:
<ns1:root xmlns:ns1="xxx"></ns1:root>

</person>
<name>theName</name>
<mandatoryaddress></mandatoryaddress>
<streetname>theStreetName</streetname>
<housenumber>123</housenumber>

<optionaladdress1></optionaladdress1>
<streetname></streetname>
<housenumber></housenumber>

<optionaladdress2></optionaladdress2>
<streetname></streetname>
<housenumber></housenumber>




I would like to obtain:
<ns1:root xmlns:ns1="xxx"></ns1:root>

</person>
<name>theName</name>
<mandatoryaddress></mandatoryaddress>
<streetname>theStreetName</streetname>
<housenumber>123</housenumber>




I also tried using nillable="true" on the OptionalAddress elements, because I would already be happy if that would generate only the OptionalAddress elements with xsi:nil attribute and without all the child elements of the Address type, e.g.

<ns1:root xmlns:ns1="xxx"></ns1:root>

</person>
<name>theName</name>
<mandatoryaddress></mandatoryaddress>
<streetname>theStreetName</streetname>
<housenumber>123</housenumber>

<optionaladdress1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"></optionaladdress1>
<optionaladdress2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"></optionaladdress2>



Can Excel do one of those "savings"?
Any changes I can make to the XSD or the Excel settings?

Additionally, the issue becomes a problem when I have xsattern defined for a mandatory child element in the complexType. Let's say StreetName is of type StreetNameType which is an xs:string with xsattern [A-Za-z]{4,20} (to give it a minimum and maximum length). Even then Excel exports the same XML file and in that case it does not validate against the same XSD schema that was used for the XML map! An empty <streetname> is not valid. Validation error contains "[facet 'pattern'] The value '' is not accepted by the pattern". Had Excel generated any of the desired output, it would validate successfully.
</streetname>

Needless to say that I have been making quite an effort to make it work and searched the web for answers ... so I hope some expert here could help me out, thanks.

 

[XmlDev]

I used HTML Off to post the text above, but since I did not provide some of the end tags, it looks a bit funny, e.g. "</person>". Let me know if it has not become clear ... and how I can make it handle XML fragments better.

 

[XmlDev]

Trying to get the Xml fragments look normal ...

XSD fragment:

            <xs:element name="Root">
                <xs:complexType>
                    <xs:sequence>
                        <xs:element name="Person" maxOccurs="unbounded">
                            <xs:complexType>
                                <xs:sequence>
                                    <xs:element name="Name" type="xs:string" minOccurs="1" maxOccurs="1"/>
                                    <xs:element name="MandatoryAddress" type="Address" minOccurs="1" maxOccurs="1"/>
                                    <xs:element name="OptionalAddress1" type="Address" minOccurs="0" maxOccurs="1"/>
                                    <xs:element name="OptionalAddress2" type="Address" minOccurs="0" maxOccurs="1"/>

Note that the OptionalAddress elements have minOccurs="0".

The complexType Address is defined in the XSD as follows:

            <xs:complexType name="Address">
                <xs:sequence minOccurs="0">
                    <xs:element name="StreetName" type="xs:string" minOccurs="1" maxOccurs="1"/>
                    <xs:element name="HouseNumber" type="xs:integer" minOccurs="0" maxOccurs="1"/>

Excel exports XML with elements for the mapped columns that are empty:

<ns1:Root xmlns:ns1="xxx">
    <Person>
        <Name>theName</Name>
        <MandatoryAddress>
            <StreetName>theStreetName</StreetName>
            <HouseNumber>123</HouseNumber>
        </MandatoryAddress>
        <OptionalAddress1>
            <StreetName/>
            <HouseNumber/>
        </OptionalAddress1>
        <OptionalAddress2>
            <StreetName/>
            <HouseNumber/>
        </OptionalAddress2>
    </Person>
</Root>

I would like to obtain:

<ns1:Root xmlns:ns1="xxx">
    <Person>
        <Name>theName</Name>
        <MandatoryAddress>
            <StreetName>theStreetName</StreetName>
            <HouseNumber>123</HouseNumber>
        </MandatoryAddress>
    </Person>
</Root>

Or when using the nillable attribute in the XSD for the optional elements:

<ns1:Root xmlns:ns1="xxx">
    <Person>
        <Name>theName</Name>
        <MandatoryAddress>
            <StreetName>theStreetName</StreetName>
            <HouseNumber>123</HouseNumber>
        </MandatoryAddress>
        <OptionalAddress1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
        <OptionalAddress2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
    </Person>
</Root>

I hope this is more readable now, but I post without preview, because that messes up the original post in the editor such that I can't even post it when the preview looks good.

Thanks in advance for your contributions.

 

[XmlDev]

Bump ... nobody has a clue? Please let me know if you need more info to understand the issue.
Since it seems a bug in Excel, at least when the generated Xml is not valid against the Xml Schema that was used for the binding (i.e. when using a pattern restriction on an optional element), should I report to Microsoft instead?
Thanks.