Geek Answers Handbook

System.InvalidCastException: Failed to convert parameter value from XElement to string

Note: I could not find this exact question in the search. I found a somewhat similar question here about stack overflow, which led me to a solution. I post a question and solution so that the next person with the problem can more easily find a solution. I would ask the CommunityWiki question, if it were possible - I was not looking for an answer to this question.

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

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 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 does not work on the line ExecuteReaderwith the exception:

System.InvalidCastException: XElement . --- > System.InvalidCastException: IConvertible

XElement ?

+5
2

SqlClient XElement .

, , System.Data.SqlTypes.SqlXml XML:

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

XmlReader, CreateReader, using.

+9

, .

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())
        });
    }
}
0

More articles:


All Articles