Importing Xml with SqlBulkCopy

I am looking into importing Xml into SQL Server 2005. The Xml files are about 30MB each, and I need the import to work fast.

The Xml is only records with fields (no real hierarchy) i.e.

<books>
<book>
<title>Harry Potter</title>
<author>JK Rowling</author>
etc etc
</book>
</books>

I am looking into doing in a C# application that will use SqlBulkCopy. However as far I can see the WriteToServer method can only accept either a DataRow[], DataTable or IDataReader.

Obviously it is very easy to convert my Xml into a DataTable to DataRow, but these are both in-memory, so they will memory intensive and slow.

Is there an easy way to expose my Xml through an IDataReader object? Like a wrapper over XmlTextReader? I have thought I could write a custom wrapper over XmlTextReader that supports the IDataReader interface - but I keep thinking what I am doing is pretty basic and I must be missing an easier solution? Anybody ideas?

If not, can anybody recommend an alternative solution for getting these records in extremely quickly programmatically?

AndrewYou can use the OPENXML command to achieve this, You would either have to pass the XML as a string to a procedure or have the file in a place SQL could access.|||I looked into OPENXML but have received several warnings regarding its performance. I wanted the fastest way of importing without consuming a lot of resources.

In the end I opted to use SQLXMLBulkLoad and it works very nice and quite elegantly (using a schema to map XML elements to SQL fields.

Andrew|||

I thought there was an XML bulk load option just couldn't find it.

Would you care to post your schema and code so this question has a complete answer.

|||

You maybe can use SQLXMLBULKLOAD .The sample code (c#)

public bool BulkLoad(string ConnectionStr, string MyXsdFile, string MyXMLFile)
{
bool test;
try
{
SQLXMLBulkLoad4 objBL = new SQLXMLBulkLoad4();
objBL.ConnectionString = @."provider=SQLOLEDB;data source=SHA-WKS1333\SQL2005;database=MatrixTest;integrated security=SSPI;";
objBL.ErrorLogFile = @.".\error.log";
objBL.CheckConstraints = true;
test = objBL.Transaction;
objBL.XMLFragment = true;
objBL.TempFilePath = @.".\";
objBL.SchemaGen = true;
objBL.SGDropTables = true;
objBL.Execute(MyXsdFile, MyXMLFile);
return true;
}
catch (Exception e)
{
MessageBox.Show("{0} Exception caught." + e);
}
return false;
}

and you can find more info about SQLXMLBULKLOAD in MSDN.

|||Could you please send the code which you have used to achieve this.

Importing Xml with SqlBulkCopy

I am looking into importing Xml into SQL Server 2005. The Xml files are about 30MB each, and I need the import to work fast.

The Xml is only records with fields (no real hierarchy) i.e.

<books>
<book>
<title>Harry Potter</title>
<author>JK Rowling</author>
etc etc
</book>
</books>

I am looking into doing in a C# application that will use SqlBulkCopy. However as far I can see the WriteToServer method can only accept either a DataRow[], DataTable or IDataReader.

Obviously it is very easy to convert my Xml into a DataTable to DataRow, but these are both in-memory, so they will memory intensive and slow.

Is there an easy way to expose my Xml through an IDataReader object? Like a wrapper over XmlTextReader? I have thought I could write a custom wrapper over XmlTextReader that supports the IDataReader interface - but I keep thinking what I am doing is pretty basic and I must be missing an easier solution? Anybody ideas?

If not, can anybody recommend an alternative solution for getting these records in extremely quickly programmatically?

AndrewYou can use the OPENXML command to achieve this, You would either have to pass the XML as a string to a procedure or have the file in a place SQL could access.|||I looked into OPENXML but have received several warnings regarding its performance. I wanted the fastest way of importing without consuming a lot of resources.

In the end I opted to use SQLXMLBulkLoad and it works very nice and quite elegantly (using a schema to map XML elements to SQL fields.

Andrew|||

I thought there was an XML bulk load option just couldn't find it.

Would you care to post your schema and code so this question has a complete answer.

|||

You maybe can use SQLXMLBULKLOAD .The sample code (c#)

public bool BulkLoad(string ConnectionStr, string MyXsdFile, string MyXMLFile)
{
bool test;
try
{
SQLXMLBulkLoad4 objBL = new SQLXMLBulkLoad4();
objBL.ConnectionString = @."provider=SQLOLEDB;data source=SHA-WKS1333\SQL2005;database=MatrixTest;integrated security=SSPI;";
objBL.ErrorLogFile = @.".\error.log";
objBL.CheckConstraints = true;
test = objBL.Transaction;
objBL.XMLFragment = true;
objBL.TempFilePath = @.".\";
objBL.SchemaGen = true;
objBL.SGDropTables = true;
objBL.Execute(MyXsdFile, MyXMLFile);
return true;
}
catch (Exception e)
{
MessageBox.Show("{0} Exception caught." + e);
}
return false;
}

and you can find more info about SQLXMLBULKLOAD in MSDN.

|||Could you please send the code which you have used to achieve this.

Importing Xml with SqlBulkCopy

I am looking into importing Xml into SQL Server 2005. The Xml files are about 30MB each, and I need the import to work fast.

The Xml is only records with fields (no real hierarchy) i.e.

<books>
<book>
<title>Harry Potter</title>
<author>JK Rowling</author>
etc etc
</book>
</books>

I am looking into doing in a C# application that will use SqlBulkCopy. However as far I can see the WriteToServer method can only accept either a DataRow[], DataTable or IDataReader.

Obviously it is very easy to convert my Xml into a DataTable to DataRow, but these are both in-memory, so they will memory intensive and slow.

Is there an easy way to expose my Xml through an IDataReader object? Like a wrapper over XmlTextReader? I have thought I could write a custom wrapper over XmlTextReader that supports the IDataReader interface - but I keep thinking what I am doing is pretty basic and I must be missing an easier solution? Anybody ideas?

If not, can anybody recommend an alternative solution for getting these records in extremely quickly programmatically?

AndrewYou can use the OPENXML command to achieve this, You would either have to pass the XML as a string to a procedure or have the file in a place SQL could access.|||I looked into OPENXML but have received several warnings regarding its performance. I wanted the fastest way of importing without consuming a lot of resources.

In the end I opted to use SQLXMLBulkLoad and it works very nice and quite elegantly (using a schema to map XML elements to SQL fields.

Andrew|||

I thought there was an XML bulk load option just couldn't find it.

Would you care to post your schema and code so this question has a complete answer.

|||

You maybe can use SQLXMLBULKLOAD .The sample code (c#)

public bool BulkLoad(string ConnectionStr, string MyXsdFile, string MyXMLFile)
{
bool test;
try
{
SQLXMLBulkLoad4 objBL = new SQLXMLBulkLoad4();
objBL.ConnectionString = @."provider=SQLOLEDB;data source=SHA-WKS1333\SQL2005;database=MatrixTest;integrated security=SSPI;";
objBL.ErrorLogFile = @.".\error.log";
objBL.CheckConstraints = true;
test = objBL.Transaction;
objBL.XMLFragment = true;
objBL.TempFilePath = @.".\";
objBL.SchemaGen = true;
objBL.SGDropTables = true;
objBL.Execute(MyXsdFile, MyXMLFile);
return true;
}
catch (Exception e)
{
MessageBox.Show("{0} Exception caught." + e);
}
return false;
}

and you can find more info about SQLXMLBULKLOAD in MSDN.

|||Could you please send the code which you have used to achieve this.sql

Importing lots of rows :-)

Simon,
Try using SqlBulkCopy object (ado.net 2.0).
AMB
"Simon Harvey" wrote:

> Hi All,
> I'm not sure how to approach this problem so if anyone could advise me
> how to do it I would be very grateful.
> I have an app that calls a web service. The webservice returns an array
> of business objects.
> So I have a collections of objects that might be, say, 10000 objects in
> size.
> I need to import all the business objects into an SQL Server 2005
> database. The only way I know how to do this is to loop through each
> object and call a stored procedure repeatedly that takes the appropriate
> parameters. The whole import must be atomic. That is, if something goes
> wrong, the whole import needs to be aborted.
> I guess I have two questions:
> 1. Is repeatedly calling a stored procedure the best way to do this sort
> of stuff? I know doing imports like this must be a pretty common
> operation, but I don't know if using an SPROC is a dumb idea.
> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
> 10000 times? Is there a better what to go about this?
> Maybe I'm worrying about nothing - but it just "feels" wrong to be
> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there
> is a more elegant way?
> Any advice anyone could offer would be very much appreciated.
> Kindest Regards
> Simon
>You're right. You don't want to call the same stored procedure 10,000 times
if there is way to accomplish the task as one unit of work. First off,
calling the stored procedure 10,000 times -while inside a transaction, will
consume and hold more resources longer than necessary.
Look into SQLBulkCopy as Alejandro offered
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:ezy9dt4mGHA.732@.TK2MSFTNGP04.phx.gbl...
> Hi All,
> I'm not sure how to approach this problem so if anyone could advise me how
> to do it I would be very grateful.
> I have an app that calls a web service. The webservice returns an array of
> business objects.
> So I have a collections of objects that might be, say, 10000 objects in
> size.
> I need to import all the business objects into an SQL Server 2005
> database. The only way I know how to do this is to loop through each
> object and call a stored procedure repeatedly that takes the appropriate
> parameters. The whole import must be atomic. That is, if something goes
> wrong, the whole import needs to be aborted.
> I guess I have two questions:
> 1. Is repeatedly calling a stored procedure the best way to do this sort
> of stuff? I know doing imports like this must be a pretty common
> operation, but I don't know if using an SPROC is a dumb idea.
> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
> 10000 times? Is there a better what to go about this?
> Maybe I'm worrying about nothing - but it just "feels" wrong to be
> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there is
> a more elegant way?
> Any advice anyone could offer would be very much appreciated.
> Kindest Regards
> Simon|||If your data is coming in as one large XML document, XML Bulk Load might be
another alternative. The final result is the same as SQLBulkCopy but the
input is an XML document.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eyYZQd5mGHA.1204@.TK2MSFTNGP03.phx.gbl...
> You're right. You don't want to call the same stored procedure 10,000
> times if there is way to accomplish the task as one unit of work. First
> off, calling the stored procedure 10,000 times -while inside a
> transaction, will consume and hold more resources longer than necessary.
> Look into SQLBulkCopy as Alejandro offered
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Simon Harvey" <nothanks@.hotmail.com> wrote in message
> news:ezy9dt4mGHA.732@.TK2MSFTNGP04.phx.gbl...
>|||Hi All,
I'm not sure how to approach this problem so if anyone could advise me
how to do it I would be very grateful.
I have an app that calls a web service. The webservice returns an array
of business objects.
So I have a collections of objects that might be, say, 10000 objects in
size.
I need to import all the business objects into an SQL Server 2005
database. The only way I know how to do this is to loop through each
object and call a stored procedure repeatedly that takes the appropriate
parameters. The whole import must be atomic. That is, if something goes
wrong, the whole import needs to be aborted.
I guess I have two questions:
1. Is repeatedly calling a stored procedure the best way to do this sort
of stuff? I know doing imports like this must be a pretty common
operation, but I don't know if using an SPROC is a dumb idea.
2. Is it ok to start a transaction and repeatedly call a SPROC, like,
10000 times? Is there a better what to go about this?
Maybe I'm worrying about nothing - but it just "feels" wrong to be
repeatedly calling an SPROC 10,000 times! :-) I was wondering if there
is a more elegant way?
Any advice anyone could offer would be very much appreciated.
Kindest Regards
Simon|||Simon,
Try using SqlBulkCopy object (ado.net 2.0).
AMB
"Simon Harvey" wrote:

> Hi All,
> I'm not sure how to approach this problem so if anyone could advise me
> how to do it I would be very grateful.
> I have an app that calls a web service. The webservice returns an array
> of business objects.
> So I have a collections of objects that might be, say, 10000 objects in
> size.
> I need to import all the business objects into an SQL Server 2005
> database. The only way I know how to do this is to loop through each
> object and call a stored procedure repeatedly that takes the appropriate
> parameters. The whole import must be atomic. That is, if something goes
> wrong, the whole import needs to be aborted.
> I guess I have two questions:
> 1. Is repeatedly calling a stored procedure the best way to do this sort
> of stuff? I know doing imports like this must be a pretty common
> operation, but I don't know if using an SPROC is a dumb idea.
> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
> 10000 times? Is there a better what to go about this?
> Maybe I'm worrying about nothing - but it just "feels" wrong to be
> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there
> is a more elegant way?
> Any advice anyone could offer would be very much appreciated.
> Kindest Regards
> Simon
>|||You're right. You don't want to call the same stored procedure 10,000 times
if there is way to accomplish the task as one unit of work. First off,
calling the stored procedure 10,000 times -while inside a transaction, will
consume and hold more resources longer than necessary.
Look into SQLBulkCopy as Alejandro offered
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:ezy9dt4mGHA.732@.TK2MSFTNGP04.phx.gbl...
> Hi All,
> I'm not sure how to approach this problem so if anyone could advise me how
> to do it I would be very grateful.
> I have an app that calls a web service. The webservice returns an array of
> business objects.
> So I have a collections of objects that might be, say, 10000 objects in
> size.
> I need to import all the business objects into an SQL Server 2005
> database. The only way I know how to do this is to loop through each
> object and call a stored procedure repeatedly that takes the appropriate
> parameters. The whole import must be atomic. That is, if something goes
> wrong, the whole import needs to be aborted.
> I guess I have two questions:
> 1. Is repeatedly calling a stored procedure the best way to do this sort
> of stuff? I know doing imports like this must be a pretty common
> operation, but I don't know if using an SPROC is a dumb idea.
> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
> 10000 times? Is there a better what to go about this?
> Maybe I'm worrying about nothing - but it just "feels" wrong to be
> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there is
> a more elegant way?
> Any advice anyone could offer would be very much appreciated.
> Kindest Regards
> Simon|||If your data is coming in as one large XML document, XML Bulk Load might be
another alternative. The final result is the same as SQLBulkCopy but the
input is an XML document.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eyYZQd5mGHA.1204@.TK2MSFTNGP03.phx.gbl...
> You're right. You don't want to call the same stored procedure 10,000
> times if there is way to accomplish the task as one unit of work. First
> off, calling the stored procedure 10,000 times -while inside a
> transaction, will consume and hold more resources longer than necessary.
> Look into SQLBulkCopy as Alejandro offered
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Simon Harvey" <nothanks@.hotmail.com> wrote in message
> news:ezy9dt4mGHA.732@.TK2MSFTNGP04.phx.gbl...
>|||Thanks guys!
That XML one seems even more interesting actually, although I'm guessing
it might be quite hard to set up the mappings. Especially seeing as the
data is quite hierarchichal in nature.
Many thanks for your advice!
Simon
Roger Wolter[MSFT] wrote:
> If your data is coming in as one large XML document, XML Bulk Load might b
e
> another alternative. The final result is the same as SQLBulkCopy but the
> input is an XML document.
>|||Thanks guys!
That XML one seems even more interesting actually, although I'm guessing
it might be quite hard to set up the mappings. Especially seeing as the
data is quite hierarchichal in nature.
Many thanks for your advice!
Simon
Roger Wolter[MSFT] wrote:
> If your data is coming in as one large XML document, XML Bulk Load might b
e
> another alternative. The final result is the same as SQLBulkCopy but the
> input is an XML document.
>

Importing CSV files with SqlBulkCopy

I am trying to import a CSV file into an SQL Server table with the OleDbDataReader and SqlBulkCopy objects, like this:

using (OleDbConnection dconn =new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\mystuff\\;Extended Properties=\"text;HDR=No;FMT=Delimited\"")){using (OleDbCommand dcmd =new OleDbCommand("select * from mytable.csv", dconn)){try{dconn.Open();using (OleDbDataReader dreader = dcmd.ExecuteReader()){try{using (SqlConnection dconn2 =new SqlConnection(@."data source=MyDBServer;initial catalog=MyDB;user id=mydbid;password=mydbpwd")){using (SqlBulkCopy bc =new SqlBulkCopy(dconn2)){try{dconn2.Open();bc.DestinationTableName ="dbo.mytable";bc.WriteToServer(dreader);}finally{dconn2.Close();}}}}finally{dreader.Close();}}}finally{dconn.Close();}}}

A couple of the columns for the destination table use abitdatatype. The CSV files uses the strings "1" and "0" to represent these.

When I run this code, it throws this exception:

Unhandled Exception: System.InvalidOperationException: The given value of type S
tring from the data source cannot be converted to type bit of the specified targ
et column. --> System.FormatException: Failed to convert parameter value from a
String to a Boolean. --> System.FormatException: String was not recognized as
a valid Boolean.
at System.Boolean.Parse(String value)
at System.String.System.IConvertible.ToBoolean(IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvid
er provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType dest
inationType)
-- End of inner exception stack trace --
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType dest
inationType)
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData
metadata)
-- End of inner exception stack trace --
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData
metadata)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount
)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at MyClass.Main()

It appears not to accept "1" and "0" as valid strings to convert to booleans. The System.Convert.ToBoolean method appears to work the same way.

Is there any way to change this behavior? I discovered if you change the "1" to "true" and "0" to "false" in the CSV file it will accept them.

Hello my friend,

I believe this is due to the way that Microsoft Access uses numbers to represent bits. I think 0 is Yes and -1 is No. Try it with 0 and -1 instead of 1 and 0.

Kind regards

Scotty

|||I created a schema.ini file for the table and specified bit columns for it. Then it converted without any problems.