Why is SQL 2005 calling CONVERT when passed a sqlxml parameter?

Question

Hi

I am declaring an SqlXml param and passing it a XmlReader. This then gets parametrized and the SQL gets executed all very well. However, when looking at the SQL which gets executed through profiler I notice that it is taking the SqlXml parameter and calling CONVERT on it...

CONVERT (xml, )

Why does the server need to call CONVERT, I've already passed it valid xml? Is it because I'm not defining a schema?

I suspect this is causing bad performance on this query. Any help would be fantastic.

Cheers

Answer 1

    ...

        command.CommandText = "SELECT * FROM MySQLFunction(@xml)";

        List<int> Ids = new List<int>() { 1, 2, 3, 4, 5};

        using (MemoryStream stream = new MemoryStream())
        {
          using (XmlWriter writer = XmlWriter.Create(stream))
          {
            writer.WriteStartElement("Element");
            foreach (int i in Ids)
            {
              writer.WriteElementString("Id", i.ToString());
            }
            writer.WriteEndElement();
            writer.Flush();

            SqlXml sxml = new SqlXml(stream);

            SqlParameter param = new SqlParameter();
            param.ParameterName = "xml";
            param.Value = sxml;
            command.Parameters.Add(param);

            SqlDataReader reader = command.ExecuteReader();

    ...

MySQLFunction

SELECT Ids.Id.value('.','int') FROM @xml.nodes('/Element/Id') as Ids(Id)

When this gets executed, SQL Server calls CONVERT (xml, '') before passing it to the function. So it looks like it gets passed as a string to SQL Server even though I've declared it as SqlXml... I would have expected it to be passed to SQL Server as xml and not conversion would be needed?

Cheers