Transferring large amounts of XML to a CLR stored procedure

Question

Hi,

I'm writing a CLR stored procedure to take XML data in the form of a string, then use the data to execute certain commands etc. The problem that I'm running into is that whenever I try to send XML that is longer than 4000 characters, I get an error, as the XmlDocument object can't load the XML as a lot of the closing tags are missing, due to the text being truncated after 4000 chars.

I think this problem boils down to the CLR stored procedure mapping the string parameter onto nvarchar(4000), when I'm thinking something like nvarchar(max) or ntext would be what I need. Unfortunately, I can't find a mapping from a .NET type onto ntext, and the string type automatically goes to nvarchar(max).

Does anyone know of a solution to my problem?

Thanks for any help

Answer 1

For CLR stored procedures, char, varchar, text, ntext, image, cursor, user-define table types and table cannot be specified as parameters.

You should be able the nvarchar(max) type instead of the ntext type.

Answer 2

Hi

I think you want the System.Data.SqlTypes.SqlXml type. For example:

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

using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void StoredProcedure1(SqlXml data)
    {
        using (XmlReader reader = data.CreateReader())
        {
            reader.MoveToContent();
            // Do stuff here.
        }
    }
};

Answer 3

ntext will disappear in future versions of SQL Server so you should use nvarchar(MAX) instead.