importing XML Data into SQL Server 2000

Hi All,
I need to import data from a large XML file.
I do not want to use XML Bulk upload or use ADO components.
I am looking for a way to use sql server scripts to open
this file through a ODBC driver , OpenRowset function.
Are there any ODBC or OLEDB drivers exist that can be used
in OpenRowset function ?
Not that I know of.
You may want to look at using a stored proc and OpenXML (although XML
Bulkload is more efficient).
Sorry.
Michael
"vaga" <anonymous@.discussions.microsoft.com> wrote in message
news:00a101c49b3b$9b4c2150$a301280a@.phx.gbl...
> Hi All,
> I need to import data from a large XML file.
> I do not want to use XML Bulk upload or use ADO components.
> I am looking for a way to use sql server scripts to open
> this file through a ODBC driver , OpenRowset function.
> Are there any ODBC or OLEDB drivers exist that can be used
> in OpenRowset function ?
>

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 DBase tables into MSDE using ADO

What is the syntax for a SQL import from DBase IV into MSDE 2000 using ADO
in VB6
I am using ...
Set cn1 = New ADODB.Connection
cn1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog='Tramcars';Data Source=PM" (Note PM is
my SQL server name and Catalog tramcars exists)
sql = "Select * Into [base] from [dbase
IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
cn1.Execute sql
I get error message 'Invalid Object Name dbase
IV;DATABASE=c:\tramcars\Bakery\.base.dbf'
---
Using...
cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
App.path & "\Data\Tramcars.mdb;Persist Security Info=False"
sql = "Select * Into [base] from [dbase
IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
cn1.Execute sql
works fine and imports DB table to Access 2000
Regards
steve
Try:
select * from OPENROWSET('MSDASQL',
'Driver={Microsoft dBase Driver};SourceDB=c:\tramcars\Bakery\;SourceType=db f',
'select * from base')
or better yet, create a linked server to dbase:
EXEC sp_addlinkedserver
'DBF',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\tramcars\Bakery\',
NULL,
'dBase IV'
exec sp_addlinkedsrvlogin
@.rmtsrvname = 'DBF',
@.useself = false,
@.locallogin = NULL,
@.rmtuser = NULL,
@.rmtpassword = NULL
select * from openquery(DBF,'select * from base')x
-oj
http://www.rac4sql.net
"steve" <sfrancis@.bigpond.net.au> wrote in message
news:OmzOkbmNEHA.3812@.TK2MSFTNGP12.phx.gbl...
> What is the syntax for a SQL import from DBase IV into MSDE 2000 using ADO
> in VB6
> I am using ...
> Set cn1 = New ADODB.Connection
> cn1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
> Security Info=False;Initial Catalog='Tramcars';Data Source=PM" (Note PM is
> my SQL server name and Catalog tramcars exists)
> sql = "Select * Into [base] from [dbase
> IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
> cn1.Execute sql
> I get error message 'Invalid Object Name dbase
> IV;DATABASE=c:\tramcars\Bakery\.base.dbf'
> Using...
> cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> App.path & "\Data\Tramcars.mdb;Persist Security Info=False"
> sql = "Select * Into [base] from [dbase
> IV;DATABASE=c:\tramcars\Bakery\].[base.dbf]"
> cn1.Execute sql
> works fine and imports DB table to Access 2000
> ----
> Regards
> steve
>
|||oj
Thanks
Worked a treat
Steve
"oj" <nospam_ojngo@.home.com> wrote in message
news:uTBcBx$NEHA.3016@.tk2msftngp13.phx.gbl...
> Try:
> select * from OPENROWSET('MSDASQL',
> 'Driver={Microsoft dBase
Driver};SourceDB=c:\tramcars\Bakery\;SourceType=db f',[vbcol=seagreen]
> 'select * from base')
> or better yet, create a linked server to dbase:
> EXEC sp_addlinkedserver
> 'DBF',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'c:\tramcars\Bakery\',
> NULL,
> 'dBase IV'
> exec sp_addlinkedsrvlogin
> @.rmtsrvname = 'DBF',
> @.useself = false,
> @.locallogin = NULL,
> @.rmtuser = NULL,
> @.rmtpassword = NULL
> select * from openquery(DBF,'select * from base')x
> --
> -oj
> http://www.rac4sql.net
>
> "steve" <sfrancis@.bigpond.net.au> wrote in message
> news:OmzOkbmNEHA.3812@.TK2MSFTNGP12.phx.gbl...
ADO[vbcol=seagreen]
Security=SSPI;Persist[vbcol=seagreen]
PM is
>