SQL Server 2005 Xml Parameter Causing Timeout?

Question

I'm trying to send a XML of aproximately 1MB as XML parameter in a Stored Procedure, but always the connection returns timeout.

Anyone knows what's the size limit for the XML type?

Environment:

Microsoft SQL Server 2005 Express
.NET Framework 2.0
C#

C# Code:

using (SqlCommand commandSave = new SqlCommand("SaveScanning", this.DatabaseConnection))
{
    commandSave.CommandType = System.Data.CommandType.StoredProcedure;

    SqlParameter scanningData = new SqlParameter("ScanningData", System.Data.SqlDbType.Xml);
    scanningData.Value = new SqlXml(new XmlTextReader(**HEREISTHEXMLSTRING**, XmlNodeType.Document, null));
    commandSave.Parameters.Add(scanningData);

    commandSave.ExecuteNonQuery();
}

SQL Code:

CREATE PROCEDURE [dbo].[SaveScanning]
(
    @ScanningData XML
)
AS
BEGIN
    .
    .
    .

When a lower XML content is passed, it works normally. (100kb) — Zanoni
– Zanoni, Commented Jun 16, 2009 at 2:27
size limit is 2gb of binary xml which is generally more compact than a textual representation of xml (e.g. qnames are tokenized in binary xml) — ahains
– ahains, Commented Jun 16, 2009 at 5:13
I guess that the problem is on SQL Server. When i'm trying to send a big XML and try to INSERT the data in a TABLE from SELECT clause, the problem occurs. When just the SELECT from XML is used, the problem doesn't occurs. — Zanoni
– Zanoni, Commented Jun 16, 2009 at 13:05

DBAndrew · Accepted Answer · 2009-06-17 16:05:05Z

1

2 GB is the max size for the XML data type. I've done XML Parameters with 20MB worth of text that result in 1000 rows inserted and this takes a quad xeon box that averages 500 user spids w/ connection pooling that sits around 25% cpu, w/ 16 GB of ram about 10 seconds.

Edit:

What XML method are you using with your SQL Select that is having the issue?

XQuery? XPath? Open XML?

If you could provide more of your T-SQL it would help.

edited Jun 17, 2009 at 16:05

answered Jun 16, 2009 at 22:42

DBAndrew

6,9782 gold badges22 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zanoni Over a year ago

That's the problem... I'm trying to insert 5000 records from a Select in XML... More than 1000 records and SQL Server returns timeout...

DBAndrew Over a year ago

5000 records is what size in XML text? The 1000 records I mentioned earlier was going into a table with about 50 columns made up of lots of varchar(50). If your 5000 records is only 5 columns wide that is nothing. When I was testing my initial XML import I recall testing up to 20,000 records which was over 400MB of xml text and IIRC it took around 2.5-3 min to complete.

John Saunders · Accepted Answer · 2009-06-16 02:29:26Z

0

Timeout means the query is taking too long, not that the parameter is too large.

Of course, it's possible that your query takes too long because the parameter is too large. Try the same query, with the same parameter, in SQL Server Management Studio (download the version for Express).

answered Jun 16, 2009 at 2:29

John Saunders

162k26 gold badges252 silver badges403 bronze badges

1 Comment

Zanoni Over a year ago

I've tried in SQL Server Management Studio and the process performed in just 2 seconds...

Zanoni · Accepted Answer · 2009-06-19 19:35:25Z

0

Finally I found a solution:

The OPENXML "function" to read XML it's so far the best way.

CREATE PROCEDURE InsertXMLData
(
    @XMLDoc XML
)
AS

Faster way:

DECLARE @handle INT

EXEC sp_xml_preparedocument @handle OUTPUT, @XMLDoc

INSERT INTO TestTable
SELECT * FROM OPENXML (@handle, '/authors', 2) WITH 
  (au_id INT,
   au_lname VARCHAR(20),
   au_fname VARCHAR(20)
  )
EXEC sp_xml_removedocument @handle

Very slow way:

INSERT INTO TestTable
SELECT
    x.item.value('@au_id', 'INT') AS au_id,
    x.item.value('@au_lname', 'VARCHAR(20)') AS au_lname
    x.item.value('@au_fname', 'VARCHAR(20)') AS au_fname
FROM
    @XMLDoc.nodes('/authors') x(item)

answered Jun 19, 2009 at 19:35

Zanoni

31.2k14 gold badges55 silver badges74 bronze badges

Collectives™ on Stack Overflow

SQL Server 2005 Xml Parameter Causing Timeout?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related