title: How to up load xml string to sql server
body: <p>I have been trying to find the answer to this question for days but can not seem to get it.</p>
  
  <p>I have an xml string obtained from a web service that I need to store in my sql server 2012.</p>
  
  <p>the xml string can be as big as 100k.</p>
  
  <p>I have the following table</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  SET ANSI_PADDING ON
  GO
  
  CREATE TABLE [dbo].[TempXML](
  [id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
  [MyXML] [varbinary](max) NOT NULL,
  CONSTRAINT [PK_TempXML] PRIMARY KEY CLUSTERED 
  (
  [id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  
  GO
  
  SET ANSI_PADDING OFF
  GO
  
  ALTER TABLE [dbo].[TempXML] ADD  CONSTRAINT [DF_TempXML_id]  DEFAULT (newid()) FOR [id]
  GO
  </code></pre>
  
  <p>And the following stored procedure</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[InsertXML] 
  (   @xml xml)
  
  AS  
  BEGIN
  
  SET NOCOUNT ON;
  Insert into TempXML
  values(NEWID(), @xml)
  END 
  </code></pre>
  
  <p>When I invoke the sproc if the data size is bigger than 43752 chars its dropping the data and just installing the id.</p>
  
  <p>On the other hand if the field is set to nvarchar(max) the max I can up load before the whole string is dropped is 65535 char's</p>
  
  <p>I am invoking the sproc from c# method</p>
  
  <pre><code>internal static void InsertTempMovie(string xml)
  {
  
  {
          xmlSouce = xmlSouce.Replace("&amp;", "&amp;amp");
          xmlSouce = xmlSouce.Replace("'", "&amp;apos;");
          xmlSouce = xmlSouce.Replace("&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;", "&lt;?xml version=\"1.0\" encoding=\"UTF-16\"?&gt;");
  
  
          string xmlSouceEncoded = HttpUtility.HtmlDecode(xmlSouce);
  
          LinQTestDataContext dbTempXML = new LinQTestDataContext();
          dbTempXML .ExecuteCommand("exec InsertTempXML @xml={0}",xmlSouceEncoded);
      }
  }
  </code></pre>
  
  <p>However I get the same result even if I invoke directly from sql server 2012 management studio</p>
  
  <p>Any ideas as to I can upload the whole string without it being dropped</p>
  
  <p>Thank you in advance</p>