SQL Server 2008 : Explaining XML - XML Schema

XML schemas provide a means to define the structure and content of an XML document. SQL Server supports a subset of the XML Schema and uses three DDL statements to manage XML schema collections: CREATE XML SCHEMA COLLECTION, ALTER XML SCHEMA COLLECTION, and DROP XML SCHEMA COLLECTION. XML schema collections are XML schemas that are imported into a SQL Server database and are defined using the XSD (XML Schema Definition) language.

We will first explore the CREATE XML SCHEMA COLLECTION DDL statement. The CREATE XML SCHEMA COLLECTION DDL uses the syntax shown in Figure 1. Refer to Table 1 for arguments and descriptions.

Figure 1. CREATE XML SCHEMA COLLECTION Syntax

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

Table 1. CREATE XML SCHEMA COLLECTION Arguments

Arguments	Description
Relational_schema	Identifies the relational schema name. If not specified, default relational schema is assumed.
sql_identifier	Is the SQL identifier for the XML schema collection.
Expression	Is a string constant or scalar variable. Is varchar, varbinary, nvarchar, or XML type.

Let’s examine the CREATE XML SCHEMA COLLECTION. In Figure 2, we created a sample XML schema collection using the CREATE XML SCHEMA COLLECTION DDL statement.

Figure 2. Sample CREATE XML SCHEMA COLLECTION

CREATE XML SCHEMA COLLECTION customerSchema AS '<schema xmlns="http://www.w3.org/2001/XMLSchema"> <element name="root" type="string"/> </schema>'

After the schema collection is created, you can then assign it to an XML variable or a column with the schema declarations it contains. You do this by referencing the schema collection in the CREATE TABLE statement shown in Figure 3.

Figure 3. CREATE TABLE statement defining the XML SCHEMA COLLECTION

CREATE TABLE [dbo].[XML_Table]( [pk] [int] IDENTITY(1,1) NOT NULL, [customerName] [varchar](255) NULL, [customerPhone] [varchar](255) NULL, [customerAddress] [xml](customerSchema) NULL, CONSTRAINT [PK_XML_Table] PRIMARY KEY CLUSTERED ( [pk] ASC ))

The ALTER XML SCHEMA COLLECTION allows you to add new schema components to an existing XML schema collection. The ALTER XML SCHEMA COLLECTION DDL uses the following syntax as shown in Figure 4. Table 2 lists all the arguments and descriptions for the ALTER XML SCHEMA COLLECTION.

Figure 4. ALTER XML SCHEMA COLLECTION Syntax

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

Table 2. ALTER XML SCHEMA COLLECTION Arguments

Argument	Description
relational_schema	Identifies the relational schema name. If not specified, the default relational schema is assumed.
sql_identifier	Is the SQL identifier for the XML schema collection.
‘Schema Component’	Is the schema component to insert.

In Figure 5, we will add a new schema component to the customerSchema.

Figure 5. Sample Usage of ALTER XML SCHEMA COLLECTION

ALTER XML SCHEMA COLLECTION customerSchema ADD '<schema xmlns="http://www.w3.org/2001/XMLSchema"> <element name="customer" type="string"/> </schema>'

The DROP XML SCHEMA COLLECTION removes an XML Schema from the database. You cannot drop an XML schema while it is in use. So if the XML Schema is associated with an XML-type parameter or column, the table column has to be altered without specifying the schema. If you try to drop the schema, while in use, you will receive an error. In the following example, Figure 6, we receive an error when we try to drop the XML Schema, customerSchema, on table XML_Table.

Figure 6. Sample Usage of DROP XML SCHEMA

DROP XML SCHEMA COLLECTION customerSchema GO Msg 6328, Level 16, State 1, Line 1 Specified collection 'customerSchema' cannot be dropped because it is used by object 'dbo.XML_Table'.

To drop an XML schema collection, we will alter the table and remove the schema on the XML column and then drop the schema, as shown in Figure 7. Refer to Table 3 for a list of arguments and descriptions for the DROP XML SCHEMA COLLECTION.

Table 3. DROP XML SCHEMA COLLECTION Arguments

Argument	Description
relational_schema	Identifies the relational schema name. If not specified, the default relational schema is assumed.
sql_identifier	Is the name of the XML schema collection to drop.

To properly drop the XML Schema, customerSchema, we need to use the ALTER TABLE statement and then execute a DROP XML SCHEMA COLLECTION. We will use syntax shown in Figure 7 to accomplish this task.

Figure 7. The ALTER TABLE Statement

ALTER TABLE XML_Table ALTER COLUMN customerAddress XML
GO
DROP XML SCHEMA COLLECTION customerSchema