SQL Bulk load - creating Schema file

  • Thread starter Thread starter Kiran
  • Start date Start date
K

Kiran

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
 
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).
 
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 said:
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
Click to expand...
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).
 
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.
 
Back
Top