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 @Employees = '1908210174'

SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Employees.nodes('/Employees/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('/Employees/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 '1908210174'





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("{0}", values[i]);
    }
    xmlString.AppendFormat("", xmlRootName);
    return xmlString.ToString();
}

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