C#/SQL - What's wrong with SqlDbType.Xml in procedures ?

Question

Hi,

I've asked few people why using xml as a parameter in stored procedure doesn't work and everyone said , that's just the way it is. I can't belive that.

command.Parameters.Add("@xmldoc", SqlDbType.Xml);

That's where compiler returns error and I can't use NVarChar beacouse it's limiteed to 4k sings. XML would be perfect as it can be 2gigs big.

How come other SqlDbTypes work well and this one retruns error ?

*

Error: Specified argument was out of the range of valid values. Parameter name: @xmldoc: Invalid SqlDbType enumeration value: 25.

*

Answer 1

Instead of using the Add Method, try using AddWithValue where you do not need to specify the type just the name and the value. Unless you are using a different direction to input?

Answer 2

It does work. You will have to set up the Value as SqlXml and not a string, but it can be done. Imagine this table:

CREATE TABLE XmlTest
(
    XmlTestId int identity(1,1) primary key
    , XmlText xml not null
)

And the sproc

CREATE PROCEDURE XmlTest_Insert
(
    @XmlText xml
)
AS

INSERT INTO XmlTest (XmlText)
VALUES (@XmlText)

Now picture a console application that looks like this:

using System.Data.SqlClient;
using System.Data;
using System.Data.SqlTypes;
using System.Xml;

namespace TestConsole
{
    class Program
    {

        static void Main(string[] args)
        {
            string xmlDoc = "<root><el1>Nothing</el1></root>";
            string connString = "server=(local);database=IntroDB;UID=sa;PWD=pwd";
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand("XmlTest_Insert", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = new SqlParameter("@XmlText", SqlDbType.Xml);
            param.Value = new SqlXml(new XmlTextReader(xmlDoc
                           , XmlNodeType.Document, null));
            cmd.Parameters.Add(param);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Dispose();
        }
    }
}

Bingo!

This was done in Visual Studio 2008 (.NET 3.5), but I am fairly sure it should work in Visual Studio 2005 (2.0 Framework), as well.