By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,512 Members | 3,520 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,512 IT Pros & Developers. It's quick & easy.

SQL Bulk load - creating Schema file

P: n/a
Hi,

I want to back up my data in some table in SQL server and import it back
using Bulk Load of SQL server 2K.

I can use the following code to backup the data in XML

dataset.WriteXml(@"C:\Data.xml");
dataset.WriteXmlSchema(@"C:\Schema1.xml");

and following code to bulk import the same data

SQLXMLBULKLOADLib.SQLXMLBulkLoad3 x = new
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
x.ConnectionString
="PROVIDER=SQLOLEDB.1;SERVER=localhost;UID=sa;PWD= ;DATABASE=demo01";
x.XMLFragment = true;
x.ErrorLogFile = @"c:\error.txt";
x.SchemaGen = true;
x.SGDropTables = true;
x.Execute(@"C:\Schema.xml",@"C:\data.xml");

this do not work, but do not thoow any error either, when I looked
closely... the format of the schema file is incorrect, if I create a correct
formate maually it works.

Does some one know how to generate schema file in correct format? using
..Net.

Regards
Kiran
Nov 16 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
This post was almost 2 weeks ago now, so I hope you got the answer by now. If not, here are my 2 bits.

As I understand it, SQLXMLBulkLoad.SchemaGen refers to creating the SQL tables into which the data will go in the BulkLoad, not to creating the schema (xsd). It takes the place of the CREATE TABLE (...) SQL command. So if true, the tables are created if they don't already exist (if they do, the data goes into the existing tables).

"Kiran" wrote:
Hi,

I want to back up my data in some table in SQL server and import it back
using Bulk Load of SQL server 2K.

I can use the following code to backup the data in XML

dataset.WriteXml(@"C:\Data.xml");
dataset.WriteXmlSchema(@"C:\Schema1.xml");

and following code to bulk import the same data

SQLXMLBULKLOADLib.SQLXMLBulkLoad3 x = new
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
x.ConnectionString
="PROVIDER=SQLOLEDB.1;SERVER=localhost;UID=sa;PWD= ;DATABASE=demo01";
x.XMLFragment = true;
x.ErrorLogFile = @"c:\error.txt";
x.SchemaGen = true;
x.SGDropTables = true;
x.Execute(@"C:\Schema.xml",@"C:\data.xml");

this do not work, but do not thoow any error either, when I looked
closely... the format of the schema file is incorrect, if I create a correct
formate maually it works.

Does some one know how to generate schema file in correct format? using
..Net.

Regards
Kiran

Nov 16 '05 #2

P: n/a
Hi Trillium,

Thanks for the reply, and NO I am still looking for a way to achieve this.

I had the impression that SchemaGen = true will drop & recreate the table.
but I don't mind if its set to false as well, all I am looking for is to be
able to load the data from XML file in to SQL tables without having to
create any files thing manually.

Regards
Kiran

"Trillium" <Tr******@discussions.microsoft.com> wrote in message
news:DD**********************************@microsof t.com...
This post was almost 2 weeks ago now, so I hope you got the answer by now. If not, here are my 2 bits.
As I understand it, SQLXMLBulkLoad.SchemaGen refers to creating the SQL tables into which the data will go in the BulkLoad, not to creating the
schema (xsd). It takes the place of the CREATE TABLE (...) SQL command. So
if true, the tables are created if they don't already exist (if they do, the
data goes into the existing tables).
"Kiran" wrote:
Hi,

I want to back up my data in some table in SQL server and import it back
using Bulk Load of SQL server 2K.

I can use the following code to backup the data in XML

dataset.WriteXml(@"C:\Data.xml");
dataset.WriteXmlSchema(@"C:\Schema1.xml");

and following code to bulk import the same data

SQLXMLBULKLOADLib.SQLXMLBulkLoad3 x = new
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
x.ConnectionString
="PROVIDER=SQLOLEDB.1;SERVER=localhost;UID=sa;PWD= ;DATABASE=demo01";
x.XMLFragment = true;
x.ErrorLogFile = @"c:\error.txt";
x.SchemaGen = true;
x.SGDropTables = true;
x.Execute(@"C:\Schema.xml",@"C:\data.xml");

this do not work, but do not thoow any error either, when I looked
closely... the format of the schema file is incorrect, if I create a correct formate maually it works.

Does some one know how to generate schema file in correct format? using
..Net.

Regards
Kiran

Nov 16 '05 #3

P: n/a
From what I understand of SQLXMLBULKLOAD, it is only for importing data. I don't think it has any ability to query the db table structure besides reporting (or not!) that an insert failed because of unmatched datatype. But for sure it has to have an existing schema as the first parameter for execute (which I THINK can be a also be stream), before it can do anything.

It seems to me that you'd have to query the database structure and create the schemas and save them somehow before doing the backup. Then use the schemas created in that process with bulkload. I have not done anything like this and have done very little with .NET yet, but it seems to me there would be something that might do the db structure/create schema in the SqlXml, SqlXmlAdapter and/or SqlXmlCommand classes. Or one of the oledb or sql client data retrieval classes.

I hope you are successful soon.

"Kiran" wrote:
Hi Trillium,

Thanks for the reply, and NO I am still looking for a way to achieve this.

I had the impression that SchemaGen = true will drop & recreate the table.
but I don't mind if its set to false as well, all I am looking for is to be
able to load the data from XML file in to SQL tables without having to
create any files thing manually.

Regards
Kiran

"Trillium" <Tr******@discussions.microsoft.com> wrote in message
news:DD**********************************@microsof t.com...
This post was almost 2 weeks ago now, so I hope you got the answer by now.

If not, here are my 2 bits.

As I understand it, SQLXMLBulkLoad.SchemaGen refers to creating the SQL

tables into which the data will go in the BulkLoad, not to creating the
schema (xsd). It takes the place of the CREATE TABLE (...) SQL command. So
if true, the tables are created if they don't already exist (if they do, the
data goes into the existing tables).

"Kiran" wrote:
Hi,

I want to back up my data in some table in SQL server and import it back
using Bulk Load of SQL server 2K.

I can use the following code to backup the data in XML

dataset.WriteXml(@"C:\Data.xml");
dataset.WriteXmlSchema(@"C:\Schema1.xml");

and following code to bulk import the same data

SQLXMLBULKLOADLib.SQLXMLBulkLoad3 x = new
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
x.ConnectionString
="PROVIDER=SQLOLEDB.1;SERVER=localhost;UID=sa;PWD= ;DATABASE=demo01";
x.XMLFragment = true;
x.ErrorLogFile = @"c:\error.txt";
x.SchemaGen = true;
x.SGDropTables = true;
x.Execute(@"C:\Schema.xml",@"C:\data.xml");

this do not work, but do not thoow any error either, when I looked
closely... the format of the schema file is incorrect, if I create a correct formate maually it works.

Does some one know how to generate schema file in correct format? using
..Net.

Regards
Kiran


Nov 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.