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)

1 comment:

Write a python program to find factorial of a number ?

Factorial  When we recursively multiply number incrementing from 1 to a given number then its called factorial of that number. We use the no...