Problems with SqlServer and large (>8K) XML strings

ServiceStack.OrmLite
1

Using OrmLite version 4.0.48 with the SqlServer Dialect Provider. I have a large XML string (over 8k) and when I try and pass that to a stored procedure (which expects an XML data type), I get an error:

XML parsing: line 131, character 59, unexpected end of input

Here’s the test code I use to generate a large XML packet:

static string GetXml(int lines)
{
	return "<xml><data>" +
	   String.Join(Environment.NewLine,
	   Enumerable.Range(1, lines)
		   .Select(i => "This is a lot of data used to make this a large XML packet.")) +
		   "</data></xml>";
}

And here’s the code that throws the exception. Note that the stored procedure name and return type are irrelevant here since the code never gets to the point of try to actually call the stored procedure:

private static void UseOrmLite()
{
	IDbConnectionFactory factory = new OrmLiteConnectionFactory(connString, SqlServerOrmLiteDialectProvider.Instance);
	using (var db = factory.OpenDbConnection())
	{
		var xml = GetXml(131);
		var result = db.SqlList<int>("xxx", cmd =>
		{
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.AddParam("inboundXML", xml, ParameterDirection.Input, DbType.Xml);
		});
	}
}

If you change the GetXml call to only do 130 lines of data, the resulting XML is around 7900 bytes and then you get an error about stored procedure “xxx” not found (or, in production, the code works fine). But leaving it at 131 lines or more creates a string > 8000 bytes and then throws the exception.

Am I passing the data incorrectly? Everything works fine if the XML is under 8k.

Thanks.

2

Sounds like you hit an SQL Server Limit.

3

Sorry, forgot to add that if I use ADO.NET directly, the large XML strings work fine.

Which part of the page you linked to makes you think it’s a SQL Server limit? I did a search for “xml” on that page and found the limit for “varchar(max), varbinary(max), xml, text, or image column” to be 2^31 - 1.

Using ADO.NET:

var xml = GetXml(200);

using (var conn = new SqlConnection(connString))
{
	conn.Open();
	using (var cmd = new SqlCommand(sprocName, conn))
	{
		cmd.CommandType = CommandType.StoredProcedure;
		var param = cmd.Parameters.Add("@inboundXML", SqlDbType.Xml);
		param.Value = xml;
		using (var rdr = cmd.ExecuteReader())
		{
			while (rdr.Read())
			{
				Console.WriteLine(rdr["GatewayMessage"]);
			}
		}
	}
}
4

That’s not using ADO.NET types, that’s using the SQL Server concrete classes directly. The difference looks like it’s specialized SqlDbType.Xml vs DbType.Xml.

You’d need to stick to using Sql Server types in that case. If it helps, you can get the concrete SqlConnection class with:

var sqlConn = (SqlConnection)db.ToDbConnection():
5

Ok. So the SqlServerOrmLiteDialectProvider doesn’t map the DbType members down to the SqlDbType members?

6

OrmLite leaves it alone, whatever’s set is what gets used.