Looking at the XML Data Type with SQL Server 2005
Question: My company is in the process of designing a new internal application. We have made the decision to go with SQL Server 2005 for our back end database and have been pretty impressed with we have seen so far. As I began the design I have been doing a lot of reading quick prototyping to validate ideas. I am trying to understand the new XML data type a bit better. We have some data that we had planned on storing in XML outside of the database but I think this would be a better solution. However, I am having a hard time figuring out how I can create tables and structures using the type and schema validation. Any ideas or examples you may have on how this can be done?
Answer:
SQL Server does introduce a new native data type for XML. This is in addition to the traditional base types that we have seen in SQL Server 2000. The goal of this data type is to enable the storage of XML values within a relational table. Natively, SQL Server 2005 stores XML values as a large binary object (BLOB). This was done to make the XML model easier and provide things like document ordering and a recursive structures. Additionally, this data type supports schema enforcement through the use of a schema collection based on XSD. Columns that are based on a schema are considered to be typed. By definition, typed XML is a variable, parameter or column that is associated with an XML schema. During the database creation the schema is registered with the database using CREATE XMLSCHEMA and then associated with the XML entity. When the XML column is updated the XML is validated against the stored schema for validity. Even though the binding is not required it really is the way to go for performance. For example, all typed XML nodes are stored using their native SQL type.
Here is an example, that I hope will help to answer your question. In this SQL Script, I create two tables. One table (SurveyTyped ) based on the schema SurveyChoices and the second table (Survey) as untyped XML.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Survey]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Survey]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SurveyTyped]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[SurveyTyped]
IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[SurveyChoices]')
DROP XML SCHEMA COLLECTION [dbo].[SurveyChoices]
CREATE XML SCHEMA COLLECTION SurveyChoices AS '
<schema xmlns="https://www.w3.org/2001/XMLSchema">
<element name="survey">
<complexType>
<sequence>
<element name="overallrating" type="string"/>
</sequence>
</complexType>
</element>
</schema>'
-- create two Survey tables, one typed and one untyped
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Survey](
SurveyID int NOT NULL,
EmployeeName varchar(50) NOT NULL,SurveyData xml)
GO
CREATE TABLE [dbo].[SurveyTyped](
SurveyID int NOT NULL,
EmployeeName varchar(50) NOT NULL,SurveyData xml(SurveyChoices) )
GO
When this script is run you end up with two tables as show in Figure 1.
Figure 1: The created tables.
As you can see the SurveyTyped table contains a schema for the XML. If you examine the properties of the SurveyData column you can see the schema declaration as shown in Figure 2.
Figure 2: The XML Schema Property.
Within the programmability menu of the database you can view the XML schema collection as shown in Figure 3.
Figure 3: Viewing the XML Schema collections.
To verify that we have created this correctly we can issue the following insert statements.
- An insert into the untyped Survey table and a select of the data
- An insert statement into the typed survey data. Notice the error that the declaration is not found as the schema was applied against the insert.
- The correct insert statement following the schema.