C#/SQL - What's wrong with SqlDbType.Xml in procedures?
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.
Comments
-
Jacob almost 2 years
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.
*
-
REA_ANDREW about 15 yearsInstead 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.
-
Jacob about 15 yearsYour 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
-
Gregory A Beamer about 15 yearsI 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.
-
netaholic over 8 yearsIMO you should provide details about your answer