Walkthrough: Create and Deploy a Database Project that Uses an XML Schema Collection

Article
02/04/2013

You can define an XML schema collection and then associate it with one or more columns in your database project. In Visual Studio, you can add a reference to an XML schema definition (XSD) file and then use the schema that it defines in the definition of your schema collection.

In this walkthrough, you will follow these steps:

Create a Schema File
Create a Database Project
Define a Schema, an XML Schema Collection, and a Table
Configure, Build, and Deploy Your Database

Prerequisites

You must have installed the Visual Studio and have access to an instance of SQL Server 2008.

Create a Schema File

Typically, you would already have an XSD file. For this walkthrough, you will create one that you can reference in your database project.

To create an XSD file

On the File menu, point to New, and click File.

The New File dialog box opens.
In the Categories list, click General.
In the Templates list, click XML Schema, and then click Open.

The schema file appears in the XML editor.

Replace the contents of the file with the following XML:

<?xml version="1.0" encoding="utf-16"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="root" type="string"/>
</schema>

On the File menu, click Save As.
Browse to the folder in which you want to create the file.
In Object name, type MySchemaCollection, and click Save.

Next, you will create a database project.

Create a Database Project

To create the source database project

On the File menu, point to New, and click Project.
Under Installed Templates, expand the Database node, and then click SQL Server.

Note

If you are using Visual Studio Professional, look under Installed Templates, expand the Database node, expand the SQL Server node, and then click Advanced.
In the list of templates, click SQL Server 2008 Database Project.

Note

If your target deployment database has a different version of SQL Server, you can specify the type of project that matches your version of SQL Server.
In Name, type MySCProject.
In Location, type or browse to the path in which you want to create the database project, and click OK.

The database project is created and appears in Solution Explorer.

Next, you will define a schema in the project.

Define a Schema, an XML Schema Collection, and a Table

To define a schema

On the View menu, click Database Schema View.
Expand the MySCProject node, expand the Schemas node, right-click the Schemas node, point to Add, and click Schema.

The Add New Item dialog box opens.
In the Templates list, click Schema.
In Name, type Person, and then click Add.
On the File menu, click Save All.

Next, you add a reference to the XSD file that contains the definition of the XML schema collection.

To add a reference to the XSD file that contains the schema collection definition

In Solution Explorer, expand the MySCProject node, right-click the References folder, and click Add XSD Reference.

The Add XSD Reference dialog box appears.
In XSD file name, type or browse to the path and the name of the XSD file that you defined in the first procedure of this walkthrough.
In the Schema list, click Person.
In Name, type ContactTypes, and then click OK.

Next, you define a table that uses the referenced schema collection.

To define a table that uses the schema collection

On the View menu, click Database Schema View.
In Schema View, expand the MySCProject node, expand the Schemas node, right-click the Person node, point to Add, and click Table.

The Add New Item dialog box opens.
In the Templates list, click Table.
In Name, type Contacts, and then click OK.

The new table is added to the database project and appears in the Transact-SQL editor.

In the Transact-SQL editor, update the table definition to match the following statements:

CREATE TABLE [Person].[Contact]
(
column_1 int NOT NULL, 
column_2 int NULL,
[AdditionalInfo] XML(CONTENT [Person].[ContactTypes]) NULL
)

On the File menu, click Save All.

Next, you build the database project.

Configure, Build, and Deploy Your Database

To configure, build, and deploy MySCProject

In Solution Explorer, right-click the MySCProject node, and click Properties.
On the properties page, click the Deploy tab.
In the Deploy action list, click Create a deployment script (.sql) and deploy to database.
Click the Edit button next to Target Database Settings.
Specify a connection to the target server where you want to deploy this database project, and then click OK.
In Target database name, type MySCProject.
On the File menu, click Save All.
In Solution Explorer, right-click the MySCProject node, and click Build.
In Solution Explorer, right-click the MySCProject node, and click Deploy.

Your database is deployed to the specified target.

Next Steps

You should check the XSD file into version control together with your database project.

Share via