XQuery method sample in SQL Server 2005

Posted at : Mar/29/2007

5315 Views

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 :

Support for xml schema. We can validate the data against the xml column when inserting new row by xml schema. Its affect to the kind of xml column type : typed xml (xml schema included), untyped xml (no xml schema included).
The ability to use XQuery against xml data stored in xml column or variables
Ability to index xml columns
Support XML-DML
Other improvement to the existing SQL Server 2000 XML functionality

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 :

Declare @data xml
Set @data = 
'
<books>
 <book>
  <title>Sql Server 2005</title>
 </book>
 <book>
  <title>C# 2005</title>
 </book>
</books>
'
--query the data to retrieve the record from the book node :
Select @data.query('books/book')

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