Step 1 : First create generic class
Step 2 : Create Collection of Employee and Convert into XML type
first add following references
- System.Xml.Linq;
- System.Xml;
- System.Data.SqlTypes;
Step 3 : Passing XML to Stored Procedure as Parameter
Step 4 : Using XML Data Type in Stored Procedure
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
