How to up load xml string to sql server

Question

I have been trying to find the answer to this question for days but can not seem to get it.

I have an xml string obtained from a web service that I need to store in my sql server 2012.

the xml string can be as big as 100k.

I have the following table

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

And the following stored procedure

CREATE PROCEDURE [dbo].[InsertXML] 
(   @xml xml)

AS  
BEGIN

SET NOCOUNT ON;
Insert into TempXML
values(NEWID(), @xml)
END 

When I invoke the sproc if the data size is bigger than 43752 chars its dropping the data and just installing the id.

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

I am invoking the sproc from c# method

internal static void InsertTempMovie(string xml)
{

{
        xmlSouce = xmlSouce.Replace("&", "&amp");
        xmlSouce = xmlSouce.Replace("'", "'");
        xmlSouce = xmlSouce.Replace("<?xml version=\"1.0\" encoding=\"UTF-8\"?>", "<?xml version=\"1.0\" encoding=\"UTF-16\"?>");


        string xmlSouceEncoded = HttpUtility.HtmlDecode(xmlSouce);

        LinQTestDataContext dbTempXML = new LinQTestDataContext();
        dbTempXML .ExecuteCommand("exec InsertTempXML @xml={0}",xmlSouceEncoded);
    }
}

However I get the same result even if I invoke directly from sql server 2012 management studio

Any ideas as to I can upload the whole string without it being dropped

Thank you in advance

Answer 1

My first recommendation would be: since you have XML data and you want to store it - store it as XML! Why convert it to varbinary? XML is not binary data...

So change your table definition to:

CREATE TABLE [dbo].[TempXML]
(
    [id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [MyXML] [XML] NOT NULL,

    CONSTRAINT [PK_TempXML] 
    PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Next, you can either use just plain ADO.NET to insert those XML files into your database - something like this:

// define connection and command in using blocks to ensure proper disposal
using(SqlConnection conn = new SqlConnection(yourConnectionString))
using (SqlCommand cmdInsert = new SqlCommand("dbo.InsertXML", conn))
{
    cmdInsert.CommandType = CommandType.StoredProcedure;

    // define your parameters
    cmdInsert.Parameters.Add("@xml", SqlDbType.Xml, int.MaxValue);

    // set the parameter values
    string xmlContents = File.ReadAllText(@"...path to your XML file here.....");
    cmdInsert.Parameters["@xml"].Value = xmlContents;

    // open connection, execute command, close connection
    conn.Open();
    int affected = cmdInsert.ExecuteNonQuery();
    conn.Close();
 }

Or if you're using Linq-to-SQL, then import the stored procedure you're using into your Linq-to-SQL data context model, and then just call that stored procedure on the DataContext class of your model:

// use your DataContext class here - in a using block
using (DataClasses1DataContext ctx = new DataClasses1DataContext())
{
    // load your XML document from a file
    XDocument doc = XDocument.Load(@"....path to your XML file here.....");

    // call the stored procedure that's defined on your DataContext class
    ctx.ProcInsertXML(doc.Root);
}

Both ways, I was able to insert very large XML (500 KB - 2.5 MB) into SQL Server without any problems whatsoever.