SQL Server XML to SQL Table

Getting data from nested XML to SQL table structure

Case Scenario

We have to get data from an XML variable with multiple departments with each departments will have employee details in its child node as shown below .

<Departments>
  <Department>
    <ID>1</ID>
    <Name>Dep1</Name>
    <Employees>
      <Employee>
        <ID>1</ID>
        <Name>Employee1</Name>
      </Employee>
      <Employee>
        <ID>2</ID>
        <Name>Employee2</Name>
      </Employee>
    </Employees>
  </Department>
  <Department>
    <ID>2</ID>
    <Name>Dep2</Name>
    <Employees>
      <Employee>
        <ID>3</ID>
        <Name>Employee3</Name>
      </Employee>
      <Employee>
        <ID>4</ID>
        <Name>Employee4</Name>
      </Employee>
    </Employees>
  </Department>
</Departments>

Solution 

DECLARE @XMLData XML = '<Departments>
<Department>
<ID>1</ID>
<Name>Dep1</Name>
<Employees>
<Employee>
<ID>1</ID>
<Name>Employee1</Name>
</Employee>
<Employee>
<ID>2</ID>
<Name>Employee2</Name>
</Employee>
</Employees>
</Department>
<Department>
<ID>2</ID>
<Name>Dep2</Name>
<Employees>
<Employee>
<ID>3</ID>
<Name>Employee3</Name>
</Employee>
<Employee>
<ID>4</ID>
<Name>Employee4</Name>
</Employee>
</Employees>
</Department>
</Departments>'


SELECT T1.Cols.value('ID[1]','INT') AS DepartmentID,
T1.Cols.value('Name[1]','NVARCHAR(255)') AS DepartmentName,
T2.Cols.value('ID[1]','INT') AS EmployeeID,
T2.Cols.value('Name[1]','NVARCHAR(255)') AS EmployeeName
FROM @XMLData.nodes('/Departments/Department') AS T1(Cols)
CROSS APPLY T1.Cols.nodes('Employees/Employee') AS T2(Cols)