Section 7.6. Managing XML Schema Collections

7.6. Managing XML Schema Collections

An XML schema collection is a metadata object in the database that contains one or more XML Schema Definition (XSD) language schemas. It is used to validate xml data type instances. You can associate XML schema collections with xml data type instances in columns or variables. An XML schema collection associated with an xml data type column validates the column instance data against the schemathe data is stored in the database if it conforms. XML schema collections are managed similarly to other database objects, using CREATE, ALTER and DROP T-SQL statements. The following subsections describe commands to create, modify, delete, and interrogate XML schema collections.

7.6.1. Creating XML Schema Collections

The CREATE XML SCHEMA COLLECTION statement is used to import XML schemas into the database. The syntax is:

     CREATE XML SCHEMA COLLECTION [<relational_schema  >.]sql_identifier AS expression 

where:

relational_schema

The name of the relational schema. If not specified, the default relational schemadbo by defaultis used.

sql_identifier

The name of the XML schema collection.

expression

The XML schema specified as a string constant or scalar variable of [n]varchar, [n]varbinary, or xml type.

The following example creates an XML schema collection for a Contact xml data type. The schema specifies that a contact has an element named Contact with three attributes: FirstName, LastName, and PhoneNumber.

     USE ProgrammingSqlServer2005     CREATE XML SCHEMA COLLECTION ContactSchemaCollection AS     N'<?xml version="1.0" encoding="utf-16"?>     <xs:schema targetNamespace="http://tempuri.org/ContactSchema"       elementFormDefault="qualified"       attributeFormDefault="unqualified"       xmlns="http://tempuri.org/ContactSchema"       xmlns:mstns="http://tempuri.org/ContactSchema"       xmlns:xs="http://www.w3.org/2001/XMLSchema">         <xs:element name="Contact">             <xs:complexType>                 <xs:sequence>                 </xs:sequence>                 <xs:attribute name="FirstName" type="xs:string" />                 <xs:attribute name="LastName" type="xs:string" />                 <xs:attribute name="PhoneNumber" type="xs:string" />             </xs:complexType>         </xs:element>     </xs:schema>' 

You can view the new XML schema collection in Object Explorer in SQL Server Management Studio by selecting Databases ProgrammingSqlServer2005 Programmability Types XML Schema Collections.

The following example creates a table with a single xml data type column named Contact. The Contact column is typed using the ContactSchemaCollection XML schema collection.

     USE ProgrammingSqlServer2005     CREATE TABLE Contacts     (         Contact xml (ContactSchemaCollection)     ) 

You insert data into a typed xml data type column in the same way as for an untyped xml data type column. The following statement adds a contact:

     INSERT INTO Contacts (Contact)     VALUES ('<Contact xmlns="http://tempuri.org/ContactSchema"       FirstName="John" LastName="Doe" PhoneNumber="555-555-5555" />') 

An error results if the data you try to insert or update in a column does not conform to the XML schema. For example, the following statement incorrectly specifies an attribute named Phone instead of PhoneNumber:

     INSERT INTO Contacts (Contact)     VALUES ('<Contact xmlns="http://tempuri.org/ContactSchema"       FirstName="John" LastName="Doe" Phone="555-555-5555" />') 

Executing the statement returns the message shown in Figure 7-12.

Figure 7-12. Message for XML schema violation example

7.6.2. Modifying XML Schema Collections

The ALTER XML SCHEMA COLLECTION statement lets you add new schemas to an existing XML schema collection created previously with the CREATE XML SCHEMA COLLECTION statement. The syntax is:

     ALTER XML SCHEMA COLLECTION [relational_schema  .]sql_identifier         ADD 'Schema Component  ' 

where:

relational_schema

The name of the relational schema. If not specified, the default relational schemadbo by defaultis used.

sql_identifier

The name of the XML schema collection to add the XML schema to.

Schema Component

The XML schema to insert into the schema collection, specified as a string constant or scalar variable of [n]varchar, [n]varbinary, or xml type.

7.6.3. Removing XML Schema Collections

The DROP XML SCHEMA COLLECTION statement deletes an entire XML schema collection previously created using the CREATE XML SCHEMA COLLECTION statement. All components of the XML schema collection are dropped. The syntax is:

     DROP XML SCHEMA COLLECTION [relational_schema.]sql_identifier 

where:

relational_schema

The name of the relational schema. If not specified, the default relational schemadbo by defaultis used.

sql_identifier

The name of the XML schema collection to drop.

The DROP XML SCHEMA COLLECTION statement is a transactional operation that can be rolled back if performed inside of a transaction.

You cannot drop an XML schema collection that is in usethat is, if any of the following are true:

• It is associated with an xml data type column or parameter.

• It is specified in a table constraint.

• It is referenced in a schema-bound function or stored procedure.

The ContactSchemaCollection XML schema collection is used by the Contacts table, so you must make the Contact column untyped by executing an ALTER TABLE T-SQL statement before you can remove the XML schema collection. The following code example uses the ALTER TABLE statement to untype the Contact column in the Contacts table:

     ALTER TABLE Contacts ALTER COLUMN Contact xml 

You can also drop either the Contact column or the Contacts table to remove the association with the XML schema collection. Once the XML schema collection is no longer associated with the Contact column, you can remove the XML schema collection by executing the DROP XML SCHEMA COLLECTION T-SQL statement, as shown in the following example:

     DROP XML SCHEMA COLLECTION ContactSchemaCollection 

7.6.4. Viewing XML Schema Collections

The catalog view xml_schema_collections enumerates the XML schema collections in a database, as shown in the following example:

     USE AdventureWorks     SELECT s.name SchemaName, xsc.name XmlSchemaCollectionName     FROM sys.xml_schema_collections xsc       JOIN sys.schemas s ON xsc.schema_id = s.schema_id 

The result set is shown in Figure 7-13.

Figure 7-13. Result set for XML schema collections example

The xml_schema_namespace intrinsic function reconstructs the XML schema collection from the schema components stored in the database and returns an xml data type instance. The syntax of the xml_schema_namespace function is:

     xml_schema_namespace ( schemaName,   xmlSchemaCollectionName,   [ targetNamespace ] ) 

where:

schemaName

The name of the XML schema in the xmlSchemaCollection

xmlSchemaCollectionName

The name of the XML schema collection

targetNamespace

The namespace URI within the XML schema collection

You can run this function against any of the rows returned by the previous query except the row representing the sys.sys schema collection. For example:

     SELECT xml_schema_namespace('Person', 'AdditionalContactInfoSchemaCollection') 

The result set is an xml data type instance that contains the Person schema in the AdditionalContactInfoSchemaCollection XML schema collection. A fragment of the result set is shown in Figure 7-14.

Figure 7-14. Fragment of results for view XML schema collection example

7.6.5. Managing XML Schema Collection Permissions

The XML schema collection permission model lets you do the following:

• Grant, revoke, and deny a user (principal) permission to create and use XML schema collections

• Transfer ownership of XML schema collections

• Use the XML schema collection objects to type xml data type columns, variables, and parameters or to constrain tables or columns

7.6.5.1. Granting permissions

You can grant a user permission to create an XML schema collection by granting the user CREATE XML SCHEMA COLLECTION permission on the database together with ALTER permission either on the schema or on the database.

For existing XML schema collection objects, you can grant CONTROL, TAKE OWNERSHIP, ALTER, EXECUTE, REFERENCES, or VIEW DEFINITION permissions using the GRANT T-SQL statement.

For more information about the GRANT T-SQL statement, see Microsoft SQL Server 2005 Books Online.

7.6.5.2. Revoking permissions

You can revoke permission to create an XML schema collection in one of the following ways:

• Revoke a user's ALTER permission on the schema. The user will no longer be able to create an XML schema collection in the relational schema.

• Revoke a user's ALTER permission on the database. The user will no longer be able to create an XML schema collection anywhere in the database.

• Revoke either CREATE XML SCHEMA COLLECTION or ALTER ANY XML SCHEMA COLLECTION from the user. This prevents the user from importing an XML schema collection in the database.

For existing XML schema collection objects, you can revoke CONTROL, TAKE OWNERSHIP, ALTER, EXECUTE, REFERENCES, or VIEW DEFINITION permissions by using the REVOKE T-SQL statement.

For more information about the REVOKE T-SQL statement, see Microsoft SQL Server 2005 Books Online.

7.6.5.3. Denying permissions

You can deny permission to create an XML schema collection in one of the following ways:

• Deny the user's ALTER permission on the schema. The user will no longer be able to create an XML schema collection anywhere in the relational schema.

• Deny the user's CONTROL permission on the schema.

• Deny the user's ALTER ANY SCHEMA permission on the database. The user will no longer be able to create an XML schema collection anywhere in the database.

For existing XML schema collection objects, you can revoke CONTROL, TAKE OWNERSHIP, ALTER, EXECUTE, REFERENCES, or VIEW DEFINITION XML schema collection permissions by using the DENY T-SQL statement.

For more information about the DENY T-SQL statement, see Microsoft SQL Server 2005 Books Online.

7.6.6. Limitations of XML Schema Collections

XML schema validation of xml data type columns is subject to some limitations:

• xsi:schemaLocation and xsi:noNamespaceSchemaLocation attribute values are ignored.

• The <xsd:include> element is not allowed.

• The <xsd:unique>, <xsd:key>, and <xsd:keyref> constraints are not supported.

• Canonical representations of values in the XML document must not violate a pattern restriction for its type.

• The processContents attribute of wildcard elements (<xsd:any> and <xsd:anyAttribute>) does not support laxit must be set to skip or strict.

• The <xsd:redefine> element is not supported.

• Types derived from xs:QName using an XML schema restriction element are not supported.

• Schemas containing list types with union type items are not supported.

• <xsd:simple> types are restricted as described in Table 7-5.

Table 7-5. <xsd:simple> types

Simple type	Restriction
duration	The year part must be in the range -231 to 231 - 1. The month, day, hour, minute, and second parts must all be in the range 0 to 9999. The second part has an additional three digits of precision after the decimal point.
dateTime	The year part must be in the range -9999 to 9999. The month part must be in the range 1 to 12. The day part must be in the range 1 to 31 and must be a valid calendar date. The hour part must be in the range -14 to 14.
date	The year part must be in the range -9999 to 9999. The month part must be in the range 1 to 12. The day part must be in the range 1 to 31 and must be a valid calendar date.
gYearMonth	The year part must be in the range -9999 to 9999.
gYear	The year part must be in the range -9999 to 9999.
gMonthDay	The month part must be in the range 1 to 12. The day part must be in the range 1 to 31.
gDay	The day part must be in the range 1 to 31.
gMonth	The month part must be in the range 1 to 12.
decimal	Must conform to the format of the SQL Server numeric data typeup to 38 digits of precision are allowed with up to 10 of those being fractional. Variable precision decimal values are not supported.
float	Must conform to the format of the SQL Server real data type.
double	Must conform to the format of the SQL Server float data type.
string	Must conform to the format of the SQL Server nvarchar(max) data type.
anyURI	Cannot be more than 4000 Unicode characters in length.

• xs:decimal instanced values are represented internally by SQL server as data type numeric (38, 10). Variable precision decimals are not supported.

• Time zone information for date, time, and dateTime simple types is normalized to Greenwich Mean Time (GMT). The GMT zone is added to data that does not have a time zone.

• The length, minLength, and maxLength facets are stored as a long data type.

• The minOccurs and maxOccurs attributes must fit in 4-byte integers.

• Schema component identifiers are limited to a length of 1000 Unicode characters. Supplementary character pairs used in some languages are not supported within identifiers.

• The NaN simple type value is not supported.

• XML schemas having types that have pattern facets or enumerations that violate those facets are rejected.

• A schema containing an <xsd:choice> particle must either have children or explicitly define its minOccurs attribute with a value of 0.

• block and final attributes cannot have repeated values.

• The namespace attribute for <xsd:any> cannot be an empty string. Explicitly specify the namespace as ##local to indicate an unqualified element or attribute.

• The uniqueness of the ID attribute is enforced only for the <xsd:attribute> component and not for the <xsd:element> component. The ID attribute for <xsd:attribute> must be unique within the schema collection.

• The NOTATION type is not supported.