Transferring large amounts of XML to a CLR stored procedure

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

From stackoverflow

• 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.

• 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.
          }
      }
  };
  

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