SQL Server 2005 allows you to store XML in a native data type. That is cool, however, having XML in the database is almost useless unless you can query the elements and attributes of the XML data natively. XQuery becomes very useful when you can use it to search based on the values of a particular element or attribute. The xml.exist() function accepts an XQuery as input and returns 0, 1, or NULL, depending on the result of the query; 0 is returned if no elements match, 1 is returned if there is a match, and NULL is returned if there is no data to query on. For example we will verify if a node exists in this particular XML string of classes.
DECLARE @XML xml
Set @XML='
<classes>
<class name="SQL Server Index"/>
<class name="SQL Precon"/>
</classes>
'
Select @XML.exist('/classes')
The code above returns a 1 since the “classes” element exists in the XML variable. If you change the XQuery expression to search for an XML node that does not exist like, Select @XML.exist('/dogs), then it will return 0.
You can see this in action as part of a CHECK CONSTRAINT. SQL Server will not allow you to use an xml.exist as part of a CHECK CONSTRAINT. So you have to first create a user defined function (UDF) to perform the action. This UDF accepts an XML field and retunes the value of an xml.exist() method looking for an instance of < Orders'>:
USE AdventureWorks
GO
CREATE FUNCTION dbo.DoesOrderXMLDataExist
(@XML XML)
RETURNS bit
AS
BEGIN
RETURN @XML.exist('/Orders')
END;
GO
To use this UDF as a CHECK CONSTRAINT, just create a table and pass the column you want to apply the constraint to the UDF you just created.
--create the table using the function
CREATE TABLE OrdersXMLCheck
(OrderDocID INT PRIMARY KEY,
xOrders XML NOT NULL Default '<Orders/>'
CONSTRAINT xml_orderconstraint
CHECK(dbo.DoesOrderXMLDataExist(xOrders)=1))
Its that simple, now you have a rule enforced on that column making sure that an <Order> element is added to this table.