Import large xml document into sql server 2005

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"Vincent Brunie" <VincentBrunie@.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|||Use Ntext for the XML parameter if your .Net version is earlier than 2.0.
Pohwan Han. Seoul. Have a nice day.
"Vincent Brunie" <VincentBrunie@.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
>|||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
"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
>|||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 complet
e
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/|||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 strea
m
coming from the client instead of a local file ?
Regards,
Vincent
"Kent Tegels" wrote:

> Hello Vincent,
>
> No, not really, because the instance of XML has to be both valid and compl
ete
> 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/
>
>|||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/