Convert Generic List to XML and Using XML DataType In Stored Procedure

Step 1 : First create generic class

 public class Employee
 {
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public string City { get; set; }
    public double Salary { get; set; }
 }

Step 2 : Create Collection of Employee and Convert into XML type

first add following references

-  System.Xml.Linq;
- System.Xml;
- System.Data.SqlTypes;

List<Employee> employeeCollection = new List<Employee>();

  var xEle = new XElement("Employees",
       from emp in employeeCollection
       select new XElement("Employee",
                   new XElement("EmployeeId", emp.EmployeeId),
                   new XElement("EmployeeName", emp.EmployeeName),
                   new XElement("City", emp.City),
                   new XElement("Salary",emp.Salary)
        )); 

Step 3 : Passing XML to Stored Procedure as Parameter

 SqlCommand cmd = new SqlCommand();
 cmd.Parameters.AddWithValue("@EmployeeXML", 

       new SqlXml(new XmlTextReader(xEle.ToString(), XmlNodeType.Document, null))); 

Step 4 : Using XML Data Type in Stored Procedure

CREATE PROCEDURE [dbo].[sp_GetEmployee]            
    @EmployeeXML XML=NULL
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @RETURNVAL INT=0
   

    DECLARE @emp TABLE(EmployeeId INT,EmployeeName VARCHAR(200),City VARCHAR(100),Salary FLOAT)
    INSERT INTO @emp
    SELECT  A.B.value('(EmployeeId)[1]', 'int' ) EmployeeId,
            A.B.value('(EmployeeName)[1]', 'varchar(200)' ) EmployeeName,
            A.B.value('(City)[1]', 'varchar(100)' ) City,
            A.B.value('(Salary)[1]', 'float' ) Salary
    FROM    @EmployeeXML.nodes('/Employees/Employee') A(B)

   SELECT * FROM @emp
END