Rully Yulian MF

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')

Select @data.query('books/book/title')

Select @data.query('data(books/book/title)')

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