XML Data type in Yukon

In Sql Server 2005, XML is a native data type with the help of it we can store XML documents itself within a SQL database. In this article let us see in details about this XML data type.

For the purpose of our understanding let us create a sample table using the code snippet provided below.

Create table DemoXMLDataType
(
EmpNumber int identity,
EmployeeDetails xml not null,
constraint Chk_EmployeeDetails check (EmployeeDetails.value('(/Employee/@DOJ)[1]', 'datetime') <= Getdate())
)
Go

As you could see EmployeeDetails is a field where XML document as such would be saved. We have added a Check constraint to check one of the attribute of the XML document and allow only those records whose DOJ (date of joining) is less than or equal to current date.

Here, [1] == means first attribute.

-- The below two Insert statements would execute successfully without any issues

Insert into DemoXMLDataType Select 'Vadivel'
Go

Insert into DemoXMLDataType Select 'Sai'
Go

-- The below statement would fail as the DOJ is greater than current date

Insert into DemoXMLDataType Select 'Vel'
Go

The above insert statement would throw an error like the one below::

Msg 547, Level 16, State 0, Line 1
INSERT statement conflicted with CHECK constraint 'Chk_EmployeeDetails'. The conflict occurred in database 'master', table 'DemoXMLDataType', column 'EmployeeDetails'. The statement has been terminated.

If we run a query to display all records in the table the EmployeeDetails field would be a hyperlink column. On clicking that hyperlink it would open the corresponding XML document in a seperate XML editor.

Select * from DemoXMLDataType
Go

[Extract from MSDN] The xml data type provides the following methods:

query() : Use this method to query over an XML instance.
value() : Use this method to retrieve a value of SQL type from an XML instance.
exist() : Use this method to determine if a query returns a nonempty result.
modify() : Use this method to specify XML DML statements to perform updates.
nodes() : Use this method to shred XML into multiple rows to propagate parts of XML documents into rowsets.

To display the Employee Number and the date of Joining use the below code snippet

Select EmpNumber, EmployeeDetails.value('(/Employee/@DOJ)[1]', 'datetime') as 'Date of Birth' from DemoXMLDataType
Go

Using the contains() XQuery function to search for a specific character string

Select * from DemoXMLDataType where EmployeeDetails.exist('//FirstName[contains(., "Vadivel")]') = 1
Go

Clean up resources
Drop table DemoXMLDataType