System.InvalidCastException: Failed to convert parameter value from a XElement to a String

Question

Note: I was unable to find this exact question in a search. I found a somewhat similar question here on Stack Overflow, which led me to the solution. I'm posting the question and the solution so that the next person with the problem can more easily find the solution. I would have made the question CommunityWiki if that were still possible - I'm not looking for rep from this.

---

I am trying to use ADO.NET to call a SQL Server 2005 stored procedure which accepts a parameter of the Xml type:

CREATE PROCEDURE dbo.SomeProcedure(
    @ListOfIds Xml)
AS
BEGIN
    DECLARE
            @Ids TABLE(ID Int);
    INSERT INTO @Ids
    SELECT ParamValues.ID.value('.', 'Int')
    FROM @ListOfIds.nodes('/Persons/id') AS ParamValues(ID);

    SELECT p.Id,
           p.FirstName,
           p.LastName
    FROM Persons AS p
         INNER JOIN @Ids AS i ON p.Id = i.ID;
END;

I pass the XML as a LINQ to XML XElement object

var idList = new XElement(
    "Persons",
    from i in selectedPeople
    select new XElement("id", i));

later

SqlCommand cmd = new SqlCommand
                 {
                     Connection = conn,
                     CommandText = "dbo.SomeProcedure",
                     CommandType = CommandType.StoredProcedure
                 };
cmd.Parameters.Add(
    new SqlParameter
    {
        ParameterName = "@ListOfIds",
        SqlDbType = SqlDbType.Xml,
        Value = idList)
    });
using (var reader = cmd.ExecuteReader())
{
    // process each row
}

This fails on the ExecuteReader line with the exception:

System.InvalidCastException: Failed to convert parameter value from a XElement to a String. ---> System.InvalidCastException: Object must implement IConvertible

What's the correct way to pass an XElement to a stored procedure?

Answer 1

The SqlClient code doesn't permit an XElement to be passed directly.

One thing you can do is to use the System.Data.SqlTypes.SqlXml class to pass the XML:

cmd.Parameters.Add(
    new SqlParameter
    {
        ParameterName = "@ListOfIds",
        SqlDbType = SqlDbType.Xml,
        Value = new SqlXml(idList.CreateReader())
    });

Depending on your code, you may need to place the XmlReader returned from the CreateReader code into a using block.

Answer 2

Here is two extension methods I built based on John Saunders' answer.

public static class ExtensionMethods
{
    public static void AddXml(this SqlParameterCollection theParameters, string name, XElement value)
    {
        theParameters.Add(new SqlParameter()
        {
            ParameterName = name,
            SqlDbType = SqlDbType.Xml,
            Value = new SqlXml(value.CreateReader())
        });
    }

    public static void AddXml(this SqlParameterCollection theParameters, string name, string value)
    {
        theParameters.Add(new SqlParameter()
        {
            ParameterName = name,
            SqlDbType = SqlDbType.Xml,
            Value = new SqlXml(XElement.Parse(value).CreateReader())
        });
    }
}