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

Question

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.

*

I'm assuming you're using at least SQL2005 and that your column is declared as an XML datatype? — GregD, Feb 22 '09 at 13:35
if you're using SQL Server 2005 onwards, there is bigger limit to the size of NVARCHAR strings. See the MAX keyword - http://msdn.microsoft.com/en-us/library/ms186939.aspx - MAX indicates that the maximum length for NVARCHAR is 1,073,741,822 — Russ Cam, Feb 22 '09 at 13:37
What does your XML parameter look like and how are you constructing it? — Russ Cam, Feb 22 '09 at 13:50
This article may be of interest - http://dotnet.sys-con.com/node/406637 - all of the code is available through hyperlinks — Russ Cam, Feb 22 '09 at 13:51
@GregD - sql2005 indeed ; don't need xml columns, stored procedure process xml and insert values to culums (tested and works fine). @Russ Cam - You're talking about sql side , for c# max is 4k. Tried that. — Jacob, Feb 22 '09 at 13:53
What version of the framework are you trying to compile with? The SqlDatatype.Xml doesn't exist in .NET 1.1. — JohannesH, Feb 22 '09 at 13:54
If it's an ASP.NET project go into the IIS settings and check that you are running under the correct runtime. — JohannesH, Feb 22 '09 at 13:56
@Jacob- I wrote an example C# console app the other day to demonstrate to a colleague that you can pass greater than 4000 length NVARCHAR string into a stored procedure. Therefore, I am adamant that you can do it. You need to select the correct CLR type to map to the NVARCHAR(MAX) SQL type — Russ Cam, Feb 22 '09 at 14:00
You can use the SqlCommand.Parameters.AddWithValue method, which I believe leaves the framework to infer the datatype of the parameter — Russ Cam, Feb 22 '09 at 14:05
If using the SqlCommand.Parameters.Add method, then I believe the correct SQLDbtype to use for NVARCHAR(MAX) is NTEXT — Russ Cam, Feb 22 '09 at 14:15
Basically, the newer VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) mapped to SQLDBTypes TEXT, NTEXT and IMAGE, respectively — Russ Cam, Feb 22 '09 at 14:19

score 15 · Accepted Answer · edited Mar 26 '13 at 14:39

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.

Your example works absoloutle fine as it should, although when I try it on my CF 3.5 app it keep saying that SqlDbType.Xml - "Specified argument was out of the range of valid values.Parameter name: @xmldoc: Invalid SqlDbType enumeration value: 25" <- Looks like enum Xml for SqlDbType doesn't exists — Jacob, Feb 22 '09 at 16:33
I did not realize it was Compact Framework. I would have to look at the rules of CF prior to altering the exercise. It is a subset of the functionality in the full .NET Framework. — Gregory A Beamer, Feb 25 '09 at 16:31

REA_ANDREW · Answer 2 · 2009-02-22T13:57:45.470

0

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?

edited Feb 22 '09 at 13:57

answered Feb 22 '09 at 13:49

REA_ANDREW

10,666
8
48
71

I've tried that , but it gave me a clue. While debuging it I've noticed that it thinkns the value is NVarChar , so my xml parameter is in wrongo format , I use : data.Document.ToString() , where data is XDocument, maybe it need the doc with codepage info ? – Jacob Feb 22 '09 at 14:15
Instead of using the .ToString() method try providing the actual object itself, as AddWithValue expects a string parameter but an object value. I would have thought that then providing your XDocument it would pick up on the format, or as you say provide a codepage. – REA_ANDREW Feb 22 '09 at 14:32

score -2 · Answer 3 · edited Sep 24 '15 at 07:41

//Create The StringWriter Object

var stringWriter = new System.IO.StringWriter();

//Create XmlSerializer Object for the serialization,
RequestUpdateRBCustomerExternal is the Class of which type having all the values

var serializer = new XmlSerializer(typeof(RequestUpdateRBCustomerExternal));

//request is of type RequestUpdateRBCustomerExternal

serializer.Serialize(stringWriter, request);

SqlXml xml = new SqlXml(new XmlTextReader(stringWriter.ToString(), XmlNodeType.Document, null));

cmd.CommandText ="insert into SAPDataTracking values('"+DateTime.Now+"','"+xml.Value+"')";

IMO you should provide details about your answer – netaholic Sep 21 '15 at 15:02 — netaholic, Sep 21 '15 at 15:02

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

3 Answers3

Linked