for xml schema collection, minOccurs="1" has been stripped out??

Question

When I go to alter schema is see in the alter statement that minOccurs="1" has been stripped out??

original

            <xsd:element name="AFE" type="string-or-empty" minOccurs="1"/>

curent

<xsd:element name="AFE" type="string-or-empty" />

Answer 1

Post a concise and complete script, which demonstrates this behaviour.

Answer 2

The default for minOccurs and maxOccurs is 1 as per W3 schools, so they only really start to matter when you need them to be values other than 1.  For example, minOccurs = 0 or maxOccurs = 2. 

For the defaults, it looks like SQL Server "shortens" them on creation but it shouldn't matter in this example.  It will not convert minOccurs = 0 or maxOccurs = 2 to shorthand.

In the following examples the two schemas behave the same:

USE tempdb
GO

-- Both schemas will behave in the same manner
CREATE XML SCHEMA COLLECTION xsc_test1
AS
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="root">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="AFE" type="xs:string"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'  
GO

CREATE XML SCHEMA COLLECTION xsc_test2
AS
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="root">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="AFE" type="xs:string" minOccurs="1" maxOccurs="1"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'  
GO


DECLARE @xml1 XML (xsc_test1), @xml2 XML (xsc_test2)

SET @xml1 = '<root><AFE>test</AFE></root>'
--SET @xml1 = '<root><AFE>test</AFE><AFE>test 2</AFE></root>'  -- both schemas will reject this
SET @xml2 = CAST( @xml1 AS XML(xsc_test2) )
GO


-- Cleanup
DROP XML SCHEMA COLLECTION xsc_test1
GO
DROP XML SCHEMA COLLECTION xsc_test2

SQL will not shorten the following XML SCHEMA COLLECTION:

USE tempdb
GO

CREATE XML SCHEMA COLLECTION xsc_test3
AS
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="root">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="AFE" type="xs:string" minOccurs="0" maxOccurs="2"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'  
GO


DECLARE @xml XML(xsc_test3)

SET @xml = '<root><AFE>test</AFE></root>'
SET @xml = '<root><AFE>test</AFE><AFE>test 2</AFE></root>'  -- both schemas will reject this
SET @xml = CAST( @xml AS XML(xsc_test3) )
GO

DROP XML SCHEMA COLLECTION xsc_test3

Answer 3

ah thanks bob, makes perfect sense. SQL is being too smart for me ;)