Import large xml document into sql server 2005

25 Nov 2005 12:51 PM

Vincent Brunie

Hi,

I am trying to import a large xml document into sql server 2005 from a c#
client.

On the server side, the database has the following structure:

urn varchar[80] : some identifier
xmlCol xml : the xml data

The following code works well for small xml documents, but I get an
OutOfMemoryException with large ones

    FileStream sr = new FileStream(@"doc.xml", FileMode.Open);

    string urn = @"urn:x-test:111";

    SqlCommand cmd = wDbConn.CreateCommand();

    cmd.CommandText = "Insert tabletest(urn, xmlCol) Values(@urn, @xmlCol)";

    SqlParameter firstColParameter =
cmd.Parameters.Add("@urn",SqlDbType.VarChar);

    firstColParameter.Value = urn;

    SqlParameter secondColParameter = cmd.Parameters.Add("@xmlCol",
SqlDbType.Variant);

    secondColParameter.Value = new SqlXml(sr); ;

    cmd.ExecuteNonQuery();

Is there a way to solve this problem ?

Best regards,

Vincent Brunie

25 Nov 2005 5:15 PM

David Browne

Show quote

"Vincent Brunie" <VincentBru***@discussions.microsoft.com> wrote in message
news:87DBBD77-9C01-4BCC-BD0C-577576D161C1@microsoft.com...
> Hi,
>
> I am trying to import a large xml document into sql server 2005 from a c#
> client.
>
> On the server side, the database has the following structure:
>
> urn varchar[80] : some identifier
> xmlCol xml : the xml data
>
> The following code works well for small xml documents, but I get an
> OutOfMemoryException with large ones
>
>    FileStream sr = new FileStream(@"doc.xml", FileMode.Open);
>
>    string urn = @"urn:x-test:111";
>
>
>
>    SqlCommand cmd = wDbConn.CreateCommand();
>
>    cmd.CommandText = "Insert tabletest(urn, xmlCol) Values(@urn,
> @xmlCol)";
>
>
>
>
>
>    SqlParameter firstColParameter =
> cmd.Parameters.Add("@urn",SqlDbType.VarChar);
>

Why are you using VarChar instead of XML for the parameter type?

David

26 Nov 2005 3:46 AM

Han

Use Ntext for the XML parameter if your .Net version is earlier than 2.0.

--
Pohwan Han. Seoul. Have a nice day.

Show quote

"Vincent Brunie" <VincentBru***@discussions.microsoft.com> wrote in message
news:87DBBD77-9C01-4BCC-BD0C-577576D161C1@microsoft.com...
> Hi,
>
> I am trying to import a large xml document into sql server 2005 from a c#
> client.
>
> On the server side, the database has the following structure:
>
> urn varchar[80] : some identifier
> xmlCol xml : the xml data
>
> The following code works well for small xml documents, but I get an
> OutOfMemoryException with large ones
>
>    FileStream sr = new FileStream(@"doc.xml", FileMode.Open);
>
>    string urn = @"urn:x-test:111";
>
>
>
>    SqlCommand cmd = wDbConn.CreateCommand();
>
>    cmd.CommandText = "Insert tabletest(urn, xmlCol) Values(@urn,
> @xmlCol)";
>
>
>
>
>
>    SqlParameter firstColParameter =
> cmd.Parameters.Add("@urn",SqlDbType.VarChar);
>
>    firstColParameter.Value = urn;
>
>
>
>    SqlParameter secondColParameter = cmd.Parameters.Add("@xmlCol",
> SqlDbType.Variant);
>
>    secondColParameter.Value = new SqlXml(sr); ;
>
>
>
>    cmd.ExecuteNonQuery();
>
>
>
> Is there a way to solve this problem ?
>
>
>
> Best regards,
>
> Vincent Brunie
>

28 Nov 2005 1:33 PM

Vincent Brunie

Hi all,

Hi,

I tried with SqlDbType.xml, SqlDbType.Varchar and SqlDbType.Text and I have
the same problem.

I work with .NET Framework 2.0.

I have the feeling that the whole xml document is loaded into memory before
being sent to the server. Is there a way to avoid this ?

Regards,

Vincent

Show quote

"Vincent Brunie" wrote:

> Hi,
>
> I am trying to import a large xml document into sql server 2005 from a c#
> client.
>
> On the server side, the database has the following structure:
>
> urn varchar[80] : some identifier
> xmlCol xml : the xml data
>
> The following code works well for small xml documents, but I get an
> OutOfMemoryException with large ones
>
>     FileStream sr = new FileStream(@"doc.xml", FileMode.Open);
>
>     string urn = @"urn:x-test:111";
>
>
>
>     SqlCommand cmd = wDbConn.CreateCommand();
>
>     cmd.CommandText = "Insert tabletest(urn, xmlCol) Values(@urn, @xmlCol)";
>
>
>
>
>
>     SqlParameter firstColParameter =
> cmd.Parameters.Add("@urn",SqlDbType.VarChar);
>
>     firstColParameter.Value = urn;
>
>
>
>     SqlParameter secondColParameter = cmd.Parameters.Add("@xmlCol",
> SqlDbType.Variant);
>
>     secondColParameter.Value = new SqlXml(sr); ;
>
>
>
>     cmd.ExecuteNonQuery();
>
>
>
> Is there a way to solve this problem ?
>
>
>
> Best regards,
>
> Vincent Brunie
>

28 Nov 2005 2:58 PM

Kent Tegels

Hello Vincent,

> I tried with SqlDbType.xml, SqlDbType.Varchar and SqlDbType.Text and I
> have the same problem.
> I work with .NET Framework 2.0.
> I have the feeling that the whole xml document is loaded into memory
> before being sent to the server. Is there a way to avoid this ?

No, not really, because the instance of XML has to be both valid and complete
at the end of the transaction.

However, if you're working with SQL Server 2005 and you can get the file
on to that server, you might try a SQL Query like this:

use scratch
go
create table dbo.xmlLoadExample
(
pkid tinyint identity(1,1) primary key
, doc xml
)
go
insert into dbo.xmlLoadExample(doc)
select * from OpenRowset(bulk N'c:\some.xml',SINGLE_BLOB) as useless
go
select doc from dbo.XmlLoadExample
go

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

29 Nov 2005 9:09 AM

Vincent Brunie

Hello Kent,

Thank you for your answer. Do you if there could be a way to put this query
into a stored procedure and to have the procedure read the data from a stream
coming from the client instead of a local file ?

Regards,
Vincent

Show quote

"Kent Tegels" wrote:

> Hello Vincent,
>
> > I tried with SqlDbType.xml, SqlDbType.Varchar and SqlDbType.Text and I
> > have the same problem.
> > I work with .NET Framework 2.0.
> > I have the feeling that the whole xml document is loaded into memory
> > before being sent to the server. Is there a way to avoid this ?
>
> No, not really, because the instance of XML has to be both valid and complete
> at the end of the transaction.
>
> However, if you're working with SQL Server 2005 and you can get the file
> on to that server, you might try a SQL Query like this:
>
> use scratch
> go
> create table dbo.xmlLoadExample
> (
> pkid tinyint identity(1,1) primary key
> , doc xml
> )
> go
> insert into dbo.xmlLoadExample(doc)
> select * from OpenRowset(bulk N'c:\some.xml',SINGLE_BLOB) as useless
> go
> select doc from dbo.XmlLoadExample
> go
>
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
>

29 Nov 2005 1:12 PM

Kent Tegels

Hello Vincent,

> Thank you for your answer. Do you if there could be a way to put this
> query into a stored procedure and to have the procedure read the data
> from a stream coming from the client instead of a local file ?

Putting the code into a stored procedure is easy. Having the procedure read
from a stream isn't. You can't really pass a stream to SQL Server as there's
no streaming data type.

I'll keep pondering on this though.

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/