Microsoft SQL Server 2008 R2 Unleashed- P191:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1894 CHAPTER 47 Using XML in SQL Server 2008 But this statement succeeds INSERT XmlExample SELECT root foo root Let s say you manage the data for a company that has just upgraded from SQL Server 2000 to 2008. You already store all your XML inside ntext columns and it s time to convert those columns to xml. You can do this easily if the stored XML is well formed as in the following example CREATE TABLE NTextXml NTextXmlColumn ntext NULL GO INSERT NTextXml SELECT feedback_review parts_order id 106 customer_comment Lot apos s of Junk customer_comment parts_order feedback_review GO ALTER TABLE NTextXml ALTER COLUMN NTextXmlColumn xml NULL Next you would like to ensure that all your XML validates against a schema. To change the column from typed to untyped XML by associating a schema you execute the following ALTER TABLE NTextXml ALTER COLUMN NTextXmlColumn xml DOCUMENT go XML Validation Declaration not found for element feedback_review . Location feedback_review 1 The statement has been terminated. Notice the error generated. The reason is that the tags used are not defined in the schemas of HRResumeSchemaCollection so the XML does not validate and the ALTER TABLE statement fails. What you really want is for the XML to validate against your own schema which is described in the next section. Using XML Schema Collections In this section you define a simple XML schema add it to a new schema collection stored on the server and create a table where you can store instances of this schema. You also add a check constraint to ensure that the value of the ProductId attribute of the XML s root node matches the value of the ProductId column using the xml data type value method discussed later in this chapter in the section The Built-in xml Data Type Using the xml Data Type 1895 Methods . The foreign key constraint you define on ProductId also serves to ensure that both ProductId values reference a primary key value in .
