Kiss the Middle-tier Goodbye with SQL Server Yukon : Page 4

Creating Constraints
You can also create constraints during the creation of a table with the use of the T-SQL function check:

CREATE TABLE Customers
(
   Col1 int primary key,
   Col2 xml check(
      Col2::exist('/Customer/@CustomerID' = 1)
)

-- The following insert operation succeeds
INSERT INTO Customers (Col1, Col2)
VALUES (1, '<Customer CustomerID="ALFKI" />')

-- The following insert operation didnt succeeds
INSERT INTO Customers (Col1, Col2)
VALUES (2, '<Customer>Alfreds Futterkiste</Customer>')

You need to check the constraint in order to make sure that the attribute CustomerID exists in the inserted XML data. The following code shows the creation of a constraint that verifies that the column CustomerID has the same value as the attribute CustomerID of the XML element <Customer>S:

CREATE TABLE Customers
(
   CustomerID int primary key,
   CustomerDocument xml,
   Constraint CustomerDocument_Check check
   (
      CustomerDocument::value('/Customer/@CustomerID', 
         'int') = CustomerID
   )
)

-- The following insert operation succeeds
INSERT INTO Customers (CustomerID, CustomerDocument)
VALUES (1, '<Customer CustomerID="1" />')

-- The following insert operation didn't succeeds
INSERT INTO Customers (CustomerID, CustomerDocument)
VALUES (2, '<Customer CustomerID="1" />')

Previous Page

Next Page