Sqlserver 2005 : xquery : Basic function

Below are some of the xquery functions that you can plug in your sql script, which has some xml parsing to do. These are just simple examples but using these concepts, one can solve bigger issues

-- generating dynamic xml
declare @Emp xml
declare @TempVar table
(Code int, FirstName nvarchar(50))
insert into @TempVar values(1,'Nishant')
insert into @TempVar values(2,'Kavita')
set @Emp = (select * from @TempVar for xml path('Employee'), root('Employees'), type)

-- use of 'query' function in xquery is to fetch perticular element of list of elements from existing xml.
declare @EmpNames xml
set @EmpNames = @Emp.query('//Employees/Employee/FirstName')
select @EmpNames for xml path('EmpNames'), type

-- use of 'nodes' and 'value' function in xquery is to get values of perticular nodes from existing xml.
declare @EmpName nvarchar(10)
select @EmpName = emp.ref.value('./FirstName[1]','nvarchar(50)')
from @Emp.nodes('//Employees/Employee') emp(ref)
where emp.ref.value('./Code[1]','nvarchar(5)') = 2
select @EmpName

-- what we have done using 'value' function can also be done with 'query' but for that we need to use 'data' function in cunjuction with 'query'.
set @EmpName = cast(@Emp.query('data(//Employees/Employee/FirstName)[2]') as nvarchar(50))
select @EmpName

-- user of 'modify' function in xquery is to insert, delete or replace elements.

-- inserting xml element
set @Emp.modify('insert Khandhadia into (//Employees/Employee)[1]')
select @Emp

-- deleting xml element
set @Emp.modify('delete (//Employees/Employee/Code)')
select @Emp

-- replacing xml element, it also has use of text() function and also let us know about how to use sql variable inside xquery.
declare @NewName nvarchar(50)
set @NewName = 'nish'
set @Emp.modify('replace value of (//Employees/Employee/FirstName/text())[1] with sql:variable("@NewName") ')
select @Emp