Requirements and limitations for XML schema collections on the server

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance

The XML schema definition language (XSD) validation has some limitations regarding SQL columns that use the xml data type. The following table provides details about those limitations and guidelines for modifying your XSD schema so it can work with SQL Server. The articles in this section provide additional information about specific limitations and guidance for working with them.

Item Limitation
minOccurs and maxOccurs The values for minOccurs and maxOccurs attributes must fit into 4-byte integers. Schemas that don't conform are rejected by the server.
<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:include> Currently, SQL Server doesn't 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 Preprocess a Schema to Merge Included Schemas.
<xsd:key>, <xsd:keyref>, and <xsd:unique> Currently, SQL Server doesn't support these XSD-based constraints for enforcing uniqueness or establishing keys and key references. XML schemas that contain these elements can't be registered.
<xsd:redefine> SQL Server doesn't support this element. For information about another way to update schemas, see The <xsd:redefine> Element.
<xsd:simpleType> values SQL Server only supports millisecond precision for simple types that have second components other than xs:time and xs:dateTime, and 100-nanosecond precision for xs:time and xs:dateTime. SQL Server puts limitations on all recognized XSD simple type enumerations.

SQL Server doesn't support using the "NaN" value in <xsd:simpleType> declarations.

For more information, seeValues for <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 doesn't support types derived from xs:QName that use an XML Schema restriction element.

SQL Server doesn't support union types with xs:QName as a member element.

For more information, see The xs:QName Type.
Adding members to an existing substitution group You can't 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.
Canonical forms and pattern restrictions The canonical representation of a value can't violate the pattern restriction for its type. For more information, see Canonical Forms and Pattern Restrictions.
Enumeration facets SQL Server doesn't support XML schemas with types that have pattern facets or enumerations that violate those facets.
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.
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 doesn't store these values. The scope for enforcement of uniqueness is the {CREATE | ALTER} XML SCHEMA COLLECTION statement.
ID type SQL Server doesn't 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.
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.
Mixed type and simple content SQL Server doesn't support restricting a mixed type to a simple content. For more information, see Mixed Type and Simple Content.
NOTATION type SQL Server doesn't support the NOTATION type.
Out-of-memory conditions When working with large XML schema collections, an out-of-memory condition might occur. For solutions to this problem, see Large XML Schema Collections and Out-of-Memory Conditions.
Repeated values SQL Server rejects schemas in which the block or final attribute has repeated values such as "restriction restriction" and "extension extension".
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 In SQL Server 2008 and later versions, time zone information is fully supported for xs:date, xs:time, and xs:dateTime values for XML Schema validation. With SQL Server 2005 (9.x) backwards-compatibility mode, time zone information is always normalized to Coordinated Universal Time (Greenwich Mean Time). 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.
Union types SQL Server doesn't support restrictions from union types.
Variable precision decimals SQL Server doesn't support 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. All xs:decimal instanced values are represented internally by the server by using the SQL type numeric (38, 10).

Next steps

See also