SQLXML bulk load with c#

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi i have dataset which i write as xml file
like
ds.WriteXmlSchema(XSDFilename);
ds.WriteXml(XMLFilename,XmlWriteMode.IgnoreSchema);

then i try to load that file into sql using below code
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
objBL.ConnectionString =
"Provider=SQLOLEDB;server=cwp01004s;database=TestBulkLoad;uid=amjad;pwd=mirror;Trust_Connection=True";
objBL.ErrorLogFile = @"c:\error.log";
objBL.KeepIdentity = false;
objBL.CheckConstraints=false;
objBL.IgnoreDuplicateKeys=false;
objBL.KeepNulls=true;

objBL.Execute (XSDFilename,XMLFilename);

i dont know for some reason i cann load to sql server while i got no errors
but still no data in sql server...... any body has any idea thanks
 
Can you give a complete example, along with the XML that you are using?
 
this the xml generated by dataset.

<?xml version="1.0" standalone="yes"?>
<agent xmlns="Fred Space">
<tblLd>
<name>mike</name>
<ID>1</ID>
</tblLd>
<tblLd>
<name>smith</name>
<ID>2</ID>
</tblLd>
<tblLd>
<name>john</name>
<ID>3</ID>
</tblLd>
<tblLd>
<name>steve</name>
<ID>4</ID>
</tblLd>
</agent>
and that is the schema generated by dataset...

<?xml version="1.0" standalone="yes"?>
<xs:schema id="agent" targetNamespace="Fred Space" xmlns:mstns="Fred Space"
xmlns="Fred Space" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="agent" msdata:IsDataSet="true" msdata:Locale="en-GB">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="tblLd">
<xs:complexType>
<xs:sequence>
<xs:element name="name" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="5" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ID" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

when i use microsoft example code its work.... but when i use dataset to
create xml and xml schema for me..... couldnot work......



Nicholas Paldino said:
Can you give a complete example, along with the XML that you are using?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

amjad said:
Hi i have dataset which i write as xml file
like
ds.WriteXmlSchema(XSDFilename);
ds.WriteXml(XMLFilename,XmlWriteMode.IgnoreSchema);

then i try to load that file into sql using below code
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
objBL.ConnectionString =
"Provider=SQLOLEDB;server=cwp01004s;database=TestBulkLoad;uid=amjad;pwd=mirror;Trust_Connection=True";
objBL.ErrorLogFile = @"c:\error.log";
objBL.KeepIdentity = false;
objBL.CheckConstraints=false;
objBL.IgnoreDuplicateKeys=false;
objBL.KeepNulls=true;

objBL.Execute (XSDFilename,XMLFilename);

i dont know for some reason i cann load to sql server while i got no
errors
but still no data in sql server...... any body has any idea thanks
Click to expand...
Click to expand...
 
Amjad,
Since your data is involved in a DataSet, you might find it easier to use
the SqlBulkCopy class that is new with ADO.NET 2.0, instead of SQLXML
BulkLoad. SqlBulkCopy takes a DataTable as input and is extremely fast.

Find out more here:

http://msdn2.microsoft.com/en-us/library/30c3y597.aspx

Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com




amjad said:
this the xml generated by dataset.

<?xml version="1.0" standalone="yes"?>
<agent xmlns="Fred Space">
<tblLd>
<name>mike</name>
<ID>1</ID>
</tblLd>
<tblLd>
<name>smith</name>
<ID>2</ID>
</tblLd>
<tblLd>
<name>john</name>
<ID>3</ID>
</tblLd>
<tblLd>
<name>steve</name>
<ID>4</ID>
</tblLd>
</agent>
and that is the schema generated by dataset...

<?xml version="1.0" standalone="yes"?>
<xs:schema id="agent" targetNamespace="Fred Space" xmlns:mstns="Fred Space"
xmlns="Fred Space" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="agent" msdata:IsDataSet="true" msdata:Locale="en-GB">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="tblLd">
<xs:complexType>
<xs:sequence>
<xs:element name="name" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="5" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ID" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

when i use microsoft example code its work.... but when i use dataset to
create xml and xml schema for me..... couldnot work......



Nicholas Paldino said:
Can you give a complete example, along with the XML that you are using?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

amjad said:
Hi i have dataset which i write as xml file
like
ds.WriteXmlSchema(XSDFilename);
ds.WriteXml(XMLFilename,XmlWriteMode.IgnoreSchema);

then i try to load that file into sql using below code
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
objBL.ConnectionString =
"Provider=SQLOLEDB;server=cwp01004s;database=TestBulkLoad;uid=amjad;pwd=mirror;Trust_Connection=True";
objBL.ErrorLogFile = @"c:\error.log";
objBL.KeepIdentity = false;
objBL.CheckConstraints=false;
objBL.IgnoreDuplicateKeys=false;
objBL.KeepNulls=true;

objBL.Execute (XSDFilename,XMLFilename);

i dont know for some reason i cann load to sql server while i got no
errors
but still no data in sql server...... any body has any idea thanks
Click to expand...
Click to expand...
Click to expand...
 
but i have vs 2003 and using net 1.1 .... :(

Peter Bromberg said:
Amjad,
Since your data is involved in a DataSet, you might find it easier to use
the SqlBulkCopy class that is new with ADO.NET 2.0, instead of SQLXML
BulkLoad. SqlBulkCopy takes a DataTable as input and is extremely fast.

Find out more here:

http://msdn2.microsoft.com/en-us/library/30c3y597.aspx

Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com




amjad said:
this the xml generated by dataset.

<?xml version="1.0" standalone="yes"?>
<agent xmlns="Fred Space">
<tblLd>
<name>mike</name>
<ID>1</ID>
</tblLd>
<tblLd>
<name>smith</name>
<ID>2</ID>
</tblLd>
<tblLd>
<name>john</name>
<ID>3</ID>
</tblLd>
<tblLd>
<name>steve</name>
<ID>4</ID>
</tblLd>
</agent>
and that is the schema generated by dataset...

<?xml version="1.0" standalone="yes"?>
<xs:schema id="agent" targetNamespace="Fred Space" xmlns:mstns="Fred Space"
xmlns="Fred Space" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="agent" msdata:IsDataSet="true" msdata:Locale="en-GB">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="tblLd">
<xs:complexType>
<xs:sequence>
<xs:element name="name" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="5" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="ID" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="1" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

when i use microsoft example code its work.... but when i use dataset to
create xml and xml schema for me..... couldnot work......



Nicholas Paldino said:
Can you give a complete example, along with the XML that you are using?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi i have dataset which i write as xml file
like
ds.WriteXmlSchema(XSDFilename);
ds.WriteXml(XMLFilename,XmlWriteMode.IgnoreSchema);

then i try to load that file into sql using below code
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
objBL.ConnectionString =
"Provider=SQLOLEDB;server=cwp01004s;database=TestBulkLoad;uid=amjad;pwd=mirror;Trust_Connection=True";
objBL.ErrorLogFile = @"c:\error.log";
objBL.KeepIdentity = false;
objBL.CheckConstraints=false;
objBL.IgnoreDuplicateKeys=false;
objBL.KeepNulls=true;

objBL.Execute (XSDFilename,XMLFilename);

i dont know for some reason i cann load to sql server while i got no
errors
but still no data in sql server...... any body has any idea thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top