Manipulating SQL SERVER database table's data with XML XPath and XQuery

Here I am assuming 1- Many relationship between Department and Employee table(i.e. A department has many employee and a single employee can work only in a single department).

Part – 1 : Generate XML type from Database tables

Suppose I need to generate a xml type for a partcular department(Here I want to generate for Dept Id 1).

Sales

Mark Louise
13/06/1990
B -ve 


Robert Langdon
21/06/1990
B -ve 


 
For this output the T-SQL would be-
 
DECLARE @XmlData;
 
SET @XmlData = SELECT dp.DepartmentId AS '@DepartmentId',dp.DepartmentName,
 
-- Employee List for that partcular department
(SELECT emp.EmployeeId AS '@EmployeeId',
               emp.EmployeeName,
                                       emp.EmployeeDOB,
                                       emp.EmployeeBloodGroup
 
                  FROM Employee emp
                    WHERE emp.FkDepartmentId = dp.DepartmentId
              FOR XML PATH('Employee'),TYPE
)
                        FROM Department as dp
WHERE   dp.DepartmentId = '1'
FOR XML PATH('Department')
 
Suppose we want to get all the departments with all their employees under a root 'Departments', then we need to just modify the T-SQL as follows.
 
SELECT dp.DepartmentId AS '@DepartmentId',dp.DepartmentName,
 
-- Employee List for that partcular department
(SELECT  emp.EmployeeId AS '@EmployeeId',
    emp.EmployeeName,
    emp.EmployeeDOB,
    emp.EmployeeBloodGroup
FROM Employee emp
WHERE emp.FkDepartmentId = dp.DepartmentId
FOR XML PATH('Department'),TYPE
)
FROM Department as dp
FOR XML PATH('Department'), ROOT('Departments')
Part - 2 : Extracting data from XML type using XQuery and XPath
--------------------------------------------------------------------------
 
We can extract data from XML type by using XQuery and XPath.
Suppose we have the above mentioned XML type(@XmlData).
In order to retrieve the data from that XML type, We have to write T-Sql something like this.
 
--  Start point
BEGIN
DECLARE @DepartmentId INT,
              @DepartmentName NVARCHAR(100)
 
-- Extract the Department information from the XMl data
SELECT @DepartmentId = @XmlData.value('(/Department/@DepartmentId)[1]', 'INT'),
            @DepartmentName = @XmlData.value('(/Department/DepartmentName)[1]', 'NVARCHAR(100)');
     
 
-- Extract each employee detail from the Department node
DECLARE @EmployeeNode XML,
      @EmployeeId INT,
      @EmployeeName VARCHAR(50),
      @EmployeeDOB NVARCHAR(50),
      @EmployeeBloodGroup NVARCHAR(10)
   
   SELECT @cnt = 1,
                       @totCnt = @XmlData.value('count(/Department/Employee)','INT')
 
  -- Loop through each emp details
          WHILE @cnt