Using Xpaths in Sql server

When we were using Sql 2000, we had very less functionalities available for xml operations. After Sql 2005, xml operations are very easy and make it very simple to handle. We have new datatype XML which allows to do lot on XML on sql server side. I am just going to take a simple example of a table which will save the xml data and how to fetch using conditions.


Ok. Here is our table. Let us call it as Contents

CREATE TABLE [dbo].[Contents](
[ContentID] [int] NOT NULL,
[ContentData] [xml] NULL,
[ContentCreatedDate] [datetime] NULL
)


We have column ContentData which is of type xml. I am going to insert few records to this table now.

insert into Contents values (1,'<customer><info id="1" fname="Subbu" lname="P"> <address address1="#444 bangalore" phone="12345"></customer>',GETDATE())

insert into Contents values (2,'<customer><info id="2" fname="Ram" lname="T"> <address address1="#111 Mumbai" phone="12345"></customer>',GETDATE())

insert into Contents values (3,'<customer><info id="3" fname="Raj" lname="M"> <address address1="#222 Pune" phone="12345"></customer>',GETDATE())

insert into Contents values (4,'<customer><info id="4" fname="Ravi" lname="R"> <address address1="#333 Mysore" phone="12345"></customer>',GETDATE())

Now let’s do a select from Contents table. You will get below result set.

You can see all the data inserted and the xml. Now lets say you need to pick the data based on the xml value. Let’s consider you need to find all the Customers from Pune. How to retrieve the data?


Select *,ContentData.value('(/Customer/Address/@Address1)[1]','varchar(20)') as Address from Contents where ContentData.value('(/Customer/Address/@Address1)[1]','varchar(20)') like '%Pune'

So here is our result set

We have passed the Xpath /Customer/Address/@Address1 to the value method which will process the xml and provide the result set. The value method takes two parameters.

With XML, we can also pass information between sps without worrying about the paramenters which gives lots of flexibility on design. I hope this small piece gives a brief idea on how to use xml and xpaths on your sql.