The XML Data Type can, just like other data types, conform to Nullability, Defaults and Constraints. If you want to make the field required (NOT NULL) and provide a default value on your XML column, just specify like you would any other column.
CREATE TABLE OrdersXML
(OrderDocID INT PRIMARY KEY,
xOrders XML NOT NULL Default '</Orders>'
This insert will work because it is relying on the default:
Insert Into OrdersXML (OrderDocID, xOrders) Values (2, DEFAULT)
The following insert will work, even if we add non <Orders> in our table because we have not declared a constraint on the column:
Insert Into OrdersXML (OrderDocID, xOrders) Values (3, 'steve')
SQL Server 2005 gives you the opportunity to add a constraint. An interesting feature of an XML column is that the CHECK constraint is based on the XML Infoset model using the methods of the XML column. You can use the exist method, which is specific to the XML type, as part of our CHECK constraint to force a particular type of XML element to exist. In the following example we will drop and recreate the table from scratch (you can use ALTER TABLE as well) adding a default and CHECK CONSTRAINT.
Drop Table OrdersXML
--create the table with the XML Datatype
--using a default value
--also has the check constraints
CREATE TABLE OrdersXML
(OrderDocID INT PRIMARY KEY,
xOrders XML NOT NULL Default '<Orders>'
CONSTRAINT xml_orderconstraint
CHECK(xOrders.exist('/Orders')=1))
Now this example from before will fail.
Insert Into OrdersXML (OrderDocID, xOrders) Values (3, 'steve')
When you attempt to insert data that will violate the xml_orderconstraint you get the following error message from SQL Server:
Msg 547, Level 16, State 0, Line 1
INSERT statement conflicted with CHECK constraint
'xml_orderconstraint'. The conflict occurred in database
'AdventureWorks', table 'OrdersXML', column 'xOrders'.
The statement has been terminated.