Sending XML to Stored Procedure

The need of sending a series of strings to Stored Procedure will be there in all most all the projects. XML variables in SQL Server make it easy to deal with XML strings into relational databases. The new methods we should use are value() and nodes() which allow us to select values from XML documents.


DECLARE @Employees xml
SET @productIds ='<Employees><id>1908</id><id>2101</id><id>74</id></Employees>' 


SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @Employees.nodes('/Products/id') as ParamValues(ID) 



The above SQL statements returns three rows as below:


1908
2101
74


Now, let us see how this can be used to fetch the Employee information for a list of Employee Ids. Take a look at the Stored Procedure.


CREATE PROCEDURE GetEmployeesDetailsForThisList(@EmployeeIds xml) AS


DECLARE @Employees TABLE (ID int) 


INSERT INTO @Employees (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @EmployeeIds.nodes('/Products/id') as ParamValues(ID) 


SELECT * FROM 
    EmployeeTable
INNER JOIN 
    @EmployeeIds e
ON    EmployeeTable.ID = e.ID




This Stored Procedure can be called as 


EXEC GetEmployeesDetailsForThisList EmployeeIds = '<Employees><id>1908</id><id>2101</id><id>74</id></Employees>' 


XML public static string BuildEmployeesXmlString(string xmlRootName, string[] values)
{
    StringBuilder xmlString = new StringBuilder();


    xmlString.AppendFormat("<{0}>", xmlRootName);
    for (int i = 0; i < values.Length; i++)
    {
    xmlString.AppendFormat("<value>{0}</value>", values[i]);
    }
    xmlString.AppendFormat("</{0}>", xmlRootName);


    return xmlString.ToString();
}


This method will return XML String, and this can be sent as the input parameter to Stored Procedure