As we already know that SQL Server 2005 now supported the xml data type. In the previous version of SQL Server we can use the FOR XML clause to retrieve the data in order to view in xml. Some new features has been added to this xml data type, some are listed below :
In this article i'll show you how can we use some XQuery method to query the xml data which stored in xml data type variable. The methods are : query(), exist(). Let us consider the following code :
the result is :
<book><title>Sql Server 2005</title></book><book><title>C# 2005</title></book>
if we want to eliminate the book element we have to change the query :
Select @data.query('books/book/title')
the result is :
<title>Sql Server 2005</title><title>C# 2005</title>
Now, how can we retrieve the data without the title element? We can add the data() function to the query paramater :
Select @data.query('data(books/book/title)')
the result is :
Sql Server 2005 C# 2005
In order to find some existing data based on the criteria input by user, Sql server has the exist() method that combine with the text() paramater added to query. I'll modify the the script above :
Declare @data xml, @exist bit
Set @data =
'
<books>
<book>
<title>Sql Server 2005</title>
</book>
<book>
<title>C# 2005</title>
</book>
</books>
'
Set @exist = @data.exist('books/book/title[text()="Sql Server 2005"]')
if (@exist = 0)
Begin
Select 'No such record'
End
else
Begin
Select 'Record(s) found'
end
the result is :
Record(s) found
By now you can see some benefits from the xml data type supported in SQL Server 2005.