Guidelines and Limitations in Using XML Schema Collections on the Server

The XML schema definition language (XSD) validation has some limitations regarding SQL columns that use the SQL Server 2005 xml data type. This topic provides details about those limitations and also guidelines for modifying your XSD schema so it can work with SQL Server. The following table lists the limitations. The sections that follow the table describe each in more detail and provide guidance for working with them.

Item Limitation

anyType

XQuery specifications recommend lax validation for elements of the anyType type. Because SQL Server does not support lax validation, strict validation is applied for elements of anyType.

minOccurs and maxOccurs

SQL Server limits the size of the values for these attributes.

sqltypes:datetime and sqltypes:smalldatetime

SQL Server does not accept sqltypes:datetime or sql:smalldatetime values.

<xsd:include>, <xsd:key>, <xsd:keyref>, <xsd:redefine>, and <xsd:unique>

SQL Server does not support these.

<xsd:choice>

SQL Server rejects schemas that have an <xsd:choice> particle without children, unless the particle is defined with a minOccurs attribute value of zero.

<xsd:simpleType> values

SQL Server only supports millisecond precision for simple types that have second components and puts limitations on all recognized XSD simple type enumerations.

SQL Server does not support using the "NaN" value in <xsd:simpleType> declarations.

xsi:schemaLocation and xsi:noNamespaceSchemaLocation

SQL Server ignores these attributes if they are present in the XML instance data inserted into a column or variable of xml data type.

xs:QName

SQL Server does not support types derived from xs:QName that use an XML Schema restriction element.

SQL Server does not support union types with xs:QName as a member element.

Adding members to an existing substitution group

SQL Server does not support adding members to an existing substitution group in an XML schema collection.

Canonical forms

Canonical representation of a value cannot violate the pattern restriction for its type.

Enumeration facets

SQL Server does not support XML schemas with types that have pattern facets or enumerations that violate those facets.

Facet length

SQL Server limits the range of acceptable values for facet length.

ID attribute

XML schema components can have an ID attribute, but SQL Server does not store these values.

ID type

SQL Server does not support elements of type xs:ID or xs:IDREF.

Lax validation

SQL Server does not support lax validation for schemas uploaded to SQL Server.

List types and union types

SQL Server does not support list types that use union types as list items.

Local namespace

SQL Server rejects schemas that use an empty string ("") as a value for the namespace attribute.

Mixed type and simple content

SQL Server does not support restricting a mixed type to a simple content.

NOTATION type

SQL Server does not support NOTATION type.

Out-of-memory conditions

In working with large XML schema collections, an out-of-memory condition may occur. Solutions are provided.

Repeated values

SQL Server rejects schemas in which the block or final attribute has repeated values.

Schema component identifiers

SQL Server limits identifiers of schema components to a maximum length of 1000 Unicode characters. Also, surrogate character pairs within identifiers are not supported.

Time zone information

Time zone information is always normalized to Coordinated Universal Time (Greenwich Mean Time).

Union types

SQL Server does not support restrictions from union types.

Variable precision decimals

SQL Server does not support variable precision decimals.

xsi:schemaLocation and xsi:noNamespaceSchemaLocation

The following attributes are ignored by SQL Server if they exist in the XML instance data inserted into a column or variable of xml data type:

  • xsi:schemaLocation
  • xsi:noNamespaceSchemaLocation

&lt;xsd:include&gt;

The World-Wide Web Consortium (W3C) XSD include element provides support for schema modularity in which an XML schema can be partitioned into more than one physical file. Currently, SQL Server does not support this element. XML schemas that include this element are rejected by the server.

As a solution, XML schemas that include the <xsd:include> directive can be preprocessed to copy and merge the contents of any included schemas into a single schema for upload to the server. For more information, see Preprocessor Tool for XML Schemas.

&lt;xsd:unique&gt;, &lt;xsd:key&gt;, and &lt;xsd:keyref&gt;

Currently, SQL Server does not support the following XSD-based constraints for enforcing uniqueness or establishing keys and key references:

  • <xsd:unique>
  • <xsd:key>
  • <xsd:keyref>

XML schemas that contain these elements cannot be registered.

Canonical Forms and Pattern Restrictions

The XSD pattern facet allows for the restriction of the lexical space of simple types. When a pattern restriction is put on a type for which there is more than one possible lexical representation, some values could cause unexpected behavior upon validation. This behavior occurs because lexical representations of these values are not stored in the database. Therefore, the values are converted to their canonical representations when serialized as output. If a document contains a value whose canonical form does not comply with the pattern restriction for its type, the document is rejected if a user tries to reinsert it.

To prevent this, SQL Server 2005 rejects any XML document that contains values that cannot be reinserted, because of the violation of pattern restrictions by their canonical forms. For example, the value "33.000" does not validate against a type derived from xs:decimal with a pattern restriction of "33\.0+". Although "33.000" complies with this pattern, the canonical form, "33", does not.

Therefore, you should be careful when you apply pattern facets to types derived from the following primitive types: boolean, decimal, float, double, dateTime, time, date, hexBinary, and base64Binary. SQL Server issues a warning when you add any such components to a schema collection.

Imprecise serialization of floating-point values has a similar problem. Because of the floating-point serialization algorithm used by SQL Server 2005, it is possible for similar values to share the same canonical form. When a floating-point value is serialized and then reinserted, its value may change slightly. In rare cases, this may result in a value that violates any of the following facets for its type on reinsertion: enumeration, minInclusive, minExclusive, maxInclusive, or maxExclusive. To prevent this, SQL Server 2005 rejects any values of types derived from xs:float or xs:double that cannot be serialized and reinserted.

Wildcard Characters and Content Validation

Wildcard characters are used to increase flexibility in what is allowed to appear in a content model. These characters are supported in the XSD language in the following ways:

  • Element wildcard characters. These are represented by the <xsd:any> element.
  • Attribute wildcard characters. These are represented by the <xsd:anyAttribute> element.

Both wildcard character elements, <xsd:any> and <xsd:anyAttribute>, support the use of a processContents attribute. This lets you specify a value that indicates how XML applications handle the validation of document content associated with these wildcard character elements. These are the different values and their effect:

  • The strict value specifies that the contents are fully validated.
  • The skip value specifies that the contents are not validated.
  • The lax value specifies that only elements and attributes for which schema definitions are available are validated.

Lax Validation

For schemas uploaded to SQL Server, lax validation is not supported. Therefore, if the processContents attribute is specified by wildcard character elements, it must be set to either skip or strict. If processContents="lax" is specified, the server rejects the schema.

Because of this behavior, any elements that are typed as xsd:anyType are validated with strict processing. As a result, their child elements and attributes must be defined in the schema collection against which the instance document is to be validated.

anyType Elements

The XQuery specifications recommend lax validation for elements of the anyType type. Because SQL Server does not support lax validation, strict validation is applied for elements of the anyType.

The following example illustrates the strict validation and creates an XML schema collection. One of the schema elements is of the anyType type. It then creates typed xml variables and illustrates the strict validation of the element of the anyType type.

CREATE XML SCHEMA COLLECTION SC AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema" 
        targetNamespace="http://ns">
   <element name="e" type="anyType"/>
   <element name="a" type="byte"/>
   <element name="b" type="string"/>
 </schema>'
GO

The following example succeeds, because the strict validation of <e> is successful:

DECLARE @var XML(SC)
SET @var = '<e xmlns="http://ns"><a>1</a><b>data</b></e>'
GO

The following example fails. The instance is rejected, because the strict validation of element <e> does not find element <c> defined in the schema:

DECLARE @var XML(SC)
SET @var = '<e xmlns="http://ns"><a>1</a><c>Wrong</c><b>data</b></e>'
GO

Again, the XML instance in the following example is rejected, because there is no declaration for element <c> in namespace http://whatever. In other words, the namespace does not exist.

DECLARE @var XML(SC)
SET @var = '<e xmlns="http://ns"><a>1</a><c xmlns="http://whatever">Wrong</c><b>data</b></e>'
SELECT @var
GO

&lt;xsd:redefine&gt;

The W3C XSD redefine element provides support for redefining schema components. However, support for this directive is potentially costly to performance and also requires that SQL Server revalidate all instances of xml data type associated with the redefined schema. Therefore, SQL Server does not support this element. XML schemas that include the <xsd:redefine> element are rejected by the server.

To update a schema or its components, you can do the following instead.

  1. Untype any xml data type (XML DT) column using the schema collection.
  2. Drop the existing XML schema collection for the affected namespace.
  3. Create a new XML schema collection for that namespace with the modified schema components.
  4. Retype all columns that were untyped during step 1 as XML DT using the new collection.

xs:QName

SQL Server does not support types derived from xs:QName by the use of an XML Schema restriction element.

Currently, SQL Server does not support union types with QName as a member type. The following CREATE XML SCHEMA COLLECTION statements cannot load the XML schema, because they specify the xs:QName type as a member type of the union:

CREATE XML SCHEMA COLLECTION QNameLimitation1 AS N'
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:simpleType name="myUnion">
        <xs:union memberTypes="xs:int xs:QName"/>
    </xs:simpleType>
</xs:schema>'
GO

CREATE XML SCHEMA COLLECTION QNameLimitation2 AS N'
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:simpleType name="myUnion">
        <xs:union memberTypes="xs:integer">
   <xs:simpleType>
    <xs:list itemType="xs:QName"/>
   </xs:simpleType>
  </xs:union>
    </xs:simpleType>
</xs:schema>'
GO

Both statements fail with an error.

Union Types As List Items

Currently, SQL Server does not support schemas that contain list types with union type items. The following example schema illustrates an attempt to support the use of a union type within a list item type:

CREATE XML SCHEMA COLLECTION MySampleCollection AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://ns" xmlns:ns="http://ns">
   <simpleType name="unionType">
      <union memberTypes="string byte"/>
   </simpleType>
   <simpleType name="listType">
      <list itemType="ns:unionType"/>
   </simpleType>
</schema>'
GO

The schema is rejected by the server with the following error:

"Invalid item type for list type 'http://ns:listType'. The item type of a list may not itself be a list, and union item types are not supported in this release."

Values for &lt;xsd:simpleType&gt;

SQL Server only supports millisecond precision for simple types that have second components. Additionally, XML instances with dateTime values that do not contain time zones are rejected on the server. The following table outlines the restrictions that are applied, based on all recognized XSD simple type enumerations.

Also, SQL Server does not support the "NaN" value in <xsd:simpleType> declarations. Schemas that include "NaN" values are rejected by the server.

Simple type Limitation

duration

The year part has to be within the range of -2^31 to 2^31-1. The month, day, hour, minute, and second must all be within the range of 0 to 9999. The seconds part has an additional three digits of precision to the right of the decimal point.

dateTime

The hour part in the time zone subfield must be within the accepted range of -14 to +14. The year part must be within the range of -9999 to 9999. The month part must be within the range of 1 to 12. The day part must be within the range of 1 to 31 and must be a valid calendar date. For example, SQL Server detects and returns an error for an invalid date, such as 1974-02-31, because the month of February does not have 31 days.

date

The year part must be within the range of -9999 to 9999. The month part must be within the range of 1 to 12. The day part must be within the range of 1 to 31 and must be a valid calendar date. For example, SQL Server detects and returns an error for an invalid date, such as 1974-02-31, because the month of February does not have 31 days.

gYearMonth

The year part must be within the range of -9999 to 9999.

gYear

The year part must be within the range of -9999 to 9999.

gMonthDay

The month part must be within the range of 1 to 12. The day part must be within the range of 1 to 31.

gDay

The day part must be within the range of 1 to 31

gMonth

The month part must be within the range of 1 to 12.

decimal

Values of this type must comply with the format of the SQL numeric type. This format internally represents the support of numbers up to a total of 38 digits, with 10 of those digit positions reserved for fractional precision.

float

Values of this type must comply with the format of the SQL real type.

double

Values of this type must comply with the format of the SQL float type.

string

Values of this type must comply with the format of the SQL nvarchar(max) type.

anyURI

Values of this type can be no more than 4000 Unicode characters in length.

Variable Precision Decimals

The xs:decimal type represents arbitrary precision decimal numbers. Minimally conforming XML processors must support decimal numbers with a minimum of totalDigits=18. SQL Server supports totalDigits=38, but limits the fractional digits to 10. SQL Server does not support variable precision decimals. All xs:decimal instanced values are represented internally by the server by using the SQL type numeric (38, 10).

Time Zone Information

For date, time, and dateTime simple types, time zone information is always normalized to Coordinated Universal Time, also referred to as Greenwich Mean Time (GMT).

For example, the following schema declares a dateTime type element named <e>:

CREATE XML SCHEMA COLLECTION MySampleCollection AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://ns" xmlns:ns="http://ns">
   <element name="e" type="dateTime"/>
</schema>'
GO

For elements of dateTime type, the server converts the time provided to GMT by using the offset value ("-05:00") and returning the corresponding GMT time.

DECLARE @var XML(MySampleCollection)
SET @var = '<e xmlns="http://ns">1999-05-31T13:20:00-05:00</e>'
SELECT @var
-- time zone is specified. Value is converted to Zulu before being stored
-- will come back as <e xmlns="http://ns">1999-05-31T18:20:00Z</e>
GO

Facet Length

The length, minLength, and maxLength facets are stored as a long type. This type is a 32-bit type. Therefore, the range of acceptable values for these values is 2^31.

minOccurs and maxOccurs

The values for minOccurs and maxOccurs attributes must fit into 4-byte integers. Schemas that do not conform are rejected by the server.

Schema Component Identifiers

SQL Server limits identifiers of schema components to a maximum length of 1000 Unicode characters and rejects schemas with identifiers that exceed the maximum length. Also, surrogate character pairs within identifiers are not supported.

Enumeration Facets

SQL Server rejects XML schemas with types that have pattern facets or enumerations that violate those facets. For example, the following schema would be rejected, because the featured enumeration value includes a mixed-case value. It would also be rejected because this value violates the pattern value that limits values to only lowercase letters.

CREATE XML SCHEMA COLLECTION MySampleCollection AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://ns" xmlns:ns="http://ns">
    <simpleType name="MyST">
       <restriction base="string">
          <pattern value="[a-z]*"/>
       </restriction>
    </simpleType>

    <simpleType name="MyST2">
       <restriction base="ns:MyST">
           <enumeration value="mYstring"/>
       </restriction>
    </simpleType>
</schema>'
GO

&lt;xsd:choice&gt;

SQL Server rejects schemas that have an <xsd:choice> particle without children, unless the particle is defined with a minOccurs attribute value of zero.

Repeated Values in a Final or Block Attribute

The block attribute allows blocking type and element substitutions from the instance. The final attribute prevents the derivation of other complex types from a type.

SQL Server rejects schemas in which the block or final attribute has repeated values such as "restriction restriction" and "extension extension".

Local Namespace

The local namespace has to be explicitly specified for the <xsd:any> element. SQL Server rejects schemas that use an empty string ("") as a value for the namespace attribute. Instead, SQL Server requires the explicit use of "##local" to indicate an unqualified element or attribute as the instance of the wildcard character.

ID Attribute

Each XML schema component can have an ID attribute on it. SQL Server enforces uniqueness for <xsd:attribute> declarations of ID type, but does not store these values. The scope for enforcement of uniqueness is the {CREATE | ALTER} XML SCHEMA COLLECTION statement.

ID Type

SQL Server does not support elements of type xs:ID, xs:IDREF, or xs:IDREFS. A schema may not declare elements of this type, or elements derived by restriction or extension from this type.

NOTATION Type

Currently, SQL Server does not support the NOTATION type. Schemas that include definitions for notations are rejected by the server.

Mixed Type and Simple Content

In the following XML schema collection, myComplexTypeA is a complex type that can be emptied. That is, both its elements have minOccurs set to 0. The attempt to restrict this to a simple content in the way it was in the myComplexTypeB declaration, is not supported. The following XML schema collection creation fails:

CREATE XML SCHEMA COLLECTION SC AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://ns" xmlns:ns="http://ns"
xmlns:ns1="http://ns1">

    <complexType name="myComplexTypeA" mixed="true">
        <sequence>
            <element name="a" type="string" minOccurs="0"/>
            <element name="b" type="string" minOccurs="0" maxOccurs="23"/>
        </sequence>
    </complexType>


    <complexType name="myComplexTypeB">
        <simpleContent>
            <restriction base="ns:myComplexTypeA">
                <simpleType>
                    <restriction base="int">
                        <minExclusive value="25"/>
                    </restriction>
                </simpleType>
            </restriction>
        </simpleContent>
    </complexType>
</schema>
'
GO

sqltypes:datetime and sqltypes:smalldatetime

In SQL Server 2005, all types derived from xs:date, xs:time, and xs:dateTime are required to have time zones. Sqltypes:datetime and sqltypes:smalldatetime are two of these types. However, the SQL datetime and smalldatetime types do not have time zones. This is because the pattern facets for sqltypes:datetime and sqltypes:smalldatetime do not allow for time zones. As a result, SQL Server does not accept sqltypes:datetime or sql:smalldatetime values.

Although you can reference sqltypes:datetime and sqltypes:smalldatetime in user-defined schemas, you cannot validate XML documents that contain values of these types. This makes them unusable. In the following example, the XML schema collection defines an element <c> of type sqltypes.datetime:

CREATE XML SCHEMA COLLECTION SC_datetime AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema" 
targetNamespace="myNS"
xmlns:ns="myNS"
xmlns:s="https://schemas.microsoft.com/sqlserver/2004/sqltypes"
> 
      <import namespace="http://www.w3.org/XML/1998/namespace"/>
      <import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes"/>
 
      <element name="root">
            <complexType>
                  <sequence>
                        <element name="c" type="s:datetime"/>
                  </sequence>
            </complexType>
      </element>
</schema>'
GO

The following assignment fails:

DECLARE @var xml(SC_datetime)
SET @var = '<x:root xmlns:x="myNS"><c>1953-01-01T00:00:00.000</c></x:root>'
GO

Adding Members to an Existing Substitution Group

You cannot add members to an existing substitution group in an XML schema collection. A substitution group in an XML schema is restricted in that the head element and all its member elements must be defined in the same {CREATE | ALTER} XML SCHEMA COLLECTION statement. For example, you can do the following:

CREATE XML SCHEMA COLLECTION col AS N'
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="e1"/>
    <xs:element name="e2" substitutionGroup="e1"/>
</xs:schema>'

However, you cannot do the following where the head element e1 is defined in one CREATE XML SCHEMA COLLECTION statement and the substitution member e2 is defined in another ALTER XML SCHEMA COLLECTION statement:

CREATE XML SCHEMA COLLECTION col AS N'
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="e1"/>
</xs:schema>'
GO

ALTER XML SCHEMA COLLECTION col add N'
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="e2" substitutionGroup="e1"/>

</xs:schema>'
GO

Union Types

Restrictions on union types are not supported. For example, the following CREATE XML SCHEMA COLLECTION statement fails:

CREATE XML SCHEMA COLLECTION particlesIk026valid AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://xsdtesting" xmlns:x="http://xsdtesting" elementFormDefault="qualified">
    <xsd:simpleType name="U1">
        <xsd:union>
            <xsd:simpleType>
                <xsd:restriction base="xsd:integer" />
            </xsd:simpleType>
            <xsd:simpleType>
                <xsd:restriction base="xsd:string" />
            </xsd:simpleType>
        </xsd:union>
    </xsd:simpleType>
    <xsd:simpleType name="U2">
        <xsd:restriction base="x:U1" />
    </xsd:simpleType>
    <xsd:complexType name="B">
        <xsd:choice>
            <xsd:element name="c1" type="x:U1" />
            <xsd:element name="c2" />
        </xsd:choice>
    </xsd:complexType>
    <xsd:complexType name="R">
        <xsd:complexContent>
            <xsd:restriction base="x:B">
                <xsd:choice>
                    <xsd:element name="c1" type="x:U2" />
                    <xsd:element name="c2" />
                </xsd:choice>
            </xsd:restriction>
        </xsd:complexContent>
    </xsd:complexType>
    <xsd:element name="doc">
        <xsd:complexType>
            <xsd:choice>
                <xsd:element name="elem" type="x:R" />
            </xsd:choice>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>'

Large XML Schema Collections and Out-of-Memory Conditions

During a call to the built-in XML_SCHEMA_NAMESPACE() function on a large XML schema collection, or when you try to drop large XML schema collections, an out-of-memory condition may occur. Following are solutions you can use to handle this:

  • When the system load is light, use the DROP_XML_SCHEMA_COLLECTION command. If this fails, put the database in single-user mode by using the ALTER DATABASE statement and trying DROP XML SCHEMA COLLECTION again. If the XML schema collection exists in master, model, or tempdb, a server restart is required for single-user mode.
  • When you call the XML_SCHEMA_NAMESPACE, you can try to retrieve a single XML schema namespace, you can try the call when the system load is lighter, or you can try the call in single-user mode.

Non-Deterministic Content Models

SQL Server rejects XML schemas that have a non-deterministic content model.

The following example attempts to create an XML schema with a non-deterministic content model. The code fails because it is not clear whether the <root> element should have a sequence of two <a> elements or if the <root> element should have two sequences, each with an <a> element.

CREATE XML SCHEMA COLLECTION MyCollection AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
    <element name="root">
        <complexType>
            <sequence minOccurs="1" maxOccurs="2">
                <element name="a" type="string" minOccurs="1" maxOccurs="2"/>
            </sequence>
        </complexType>
    </element>
</schema>
'
GO

The schema can be fixed by moving the occurrence constraint to a unique location. For example, the constraint can be moved to the containing sequence particle:

<sequence minOccurs="1" maxOccurs="4">
    <element name="a" type="string" minOccurs="1" maxOccurs="1"/>
</sequence>

Or the constraint can be moved to the contained element:

<sequence minOccurs="1" maxOccurs="1">
     <element name="a" type="string" minOccurs="1" maxOccurs="4"/>
</sequence>

SQL Server 2005 Service Pack 1 Behavior

Non-deterministic content models are accepted if the occurrence constraints are 0,1, or unbounded.

The following example is rejected in SQL Server 2005 but is accepted by a server running SQL Server SP1.

CREATE XML SCHEMA COLLECTION MyCollection AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
    <element name="root">
        <complexType>
            <sequence minOccurs="0" maxOccurs="unbounded">
                <element name="a" type="string" minOccurs="0" maxOccurs="1"/>
                <element name="b" type="string" minOccurs="1" maxOccurs="unbounded"/>
            </sequence>
        </complexType>
    </element>
</schema>
'
GO

See Also

Reference

Guidelines and Limitations in Using XML Schema Collections on the Server
Permissions on an XML Schema Collection
Managing XML Schema Collections on the Server

Concepts

xml Data Type
Typed vs. Untyped XML
Understanding the Unique Particle Attribution Constraint

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006

New content:
  • Added a section that describes how non-deterministic content models are treated in SP1.