7.6. Managing XML Schema CollectionsAn 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 CollectionsThe 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:
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 example7.6.2. Modifying XML Schema CollectionsThe 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:
7.6.3. Removing XML Schema CollectionsThe 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:
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:
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 CollectionsThe 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 exampleThe 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:
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 example7.6.5. Managing XML Schema Collection PermissionsThe XML schema collection permission model lets you do the following:
7.6.5.1. Granting permissionsYou 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.
7.6.5.2. Revoking permissionsYou can revoke permission to create an XML schema collection in one of the following ways:
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.
7.6.5.3. Denying permissionsYou can deny permission to create an XML schema collection in one of the following ways:
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.
7.6.6. Limitations of XML Schema CollectionsXML schema validation of xml data type columns is subject to some limitations:
|