June 7, 2005 at 4:41 pm
Hello all,
I am the guy who posted a question about inserting huge record into the database. Now I figured out by using SQLXMLBulkLoad is much easier and faster to accomplish my task. However, I still encounter several problems when doing this:
This is my code for bulkloading:
objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
objBL.ConnectionString = "Connection String"
objBL.ErrorLogFile = "C:\error.log"
objBL.Execute("c:\leadsMapping.xml", "c:\objDoc.xml")
objBL =
Nothing
1. Everytime I execute this code, I got an error if the same data is inserted twice. I have to delete all the existing identical record in the designated database then the code will run fine. Is there a way I could update the existing record when doing a SQLXMLbulkloading?
2. In the line 'objBL.Execute("c:\leadsMapping.xml", "c:\objDoc.xml")' , i need to insert the data in objDoc.xml to two tables - one I have defined in leadsMapping.xml, the other one is defined in another xml file (let's call it b.xml). This is what it is like in those two xml files (leadsMapping's tag is identical to objDoc.xml, while b.xml contains only part of the tags in objDoc.xml):
leadsMapping.xml
<?
xml version="1.0" ?>
<
Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="DateTimeStamp" dt:type="string" />
<ElementType name="EMail" dt:type="string" />
<ElementType name="UniqueId" dt:type="string" />
<ElementType name="FName" dt:type="string" />
<ElementType name="LName" dt:type="string" />
<ElementType name="Phone" dt:type="string" />
<ElementType name="Street" dt:type="string" />
<ElementType name="City" dt:type="string" />
<ElementType name="Province" dt:type="string" />
<ElementType name="PostalCode" dt:type="string" />
<ElementType name="Country" dt:type="string" />
<ElementType name="IPAddress" dt:type="string" />
<ElementType name="Leads" sql:is-constant="1">
<element type="lead" />
</ElementType>
<ElementType name="lead" sql:relation="leads">
<element type="DateTimeStamp" sql:field="DateTimeStamp" />
<element type="EMail" sql:field="EMailAddress" />
<element type="UniqueId" sql:field="UniqueId" />
<element type="FName" sql:field="FName" />
<element type="LName" sql:field="LName" />
<element type="Phone" sql:field="Phone" />
<element type="Street" sql:field="Street" />
<element type="City" sql:field="City" />
<element type="Province" sql:field="Province" />
<element type="PostalCode" sql:field="PostalCode" />
<element type="Country" sql:field="Country" />
<element type="IPAddress" sql:field="IPAddress" />
sql:field="LeadSharedEMailAddress" />
</ElementType>
</
Schema>
b.xml
<?
xml version="1.0" ?>
<
Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="DateTimeStamp" dt:type="string" />
<ElementType name="EMail" dt:type="string" />
<ElementType name="UniqueId" dt:type="string" />
<ElementType name="FName" dt:type="string" />
<ElementType name="LName" dt:type="string" />
<ElementType name="IPAddress" dt:type="string" />
<ElementType name="Leads" sql:is-constant="1">
<element type="lead" />
</ElementType>
<ElementType name="lead" sql:relation="leads">
<element type="DateTimeStamp" sql:field="DateTimeStamp" />
<element type="EMail" sql:field="EMailAddress" />
<element type="UniqueId" sql:field="UniqueId" />
<element type="FName" sql:field="FName" />
<element type="LName" sql:field="LName" />
<element type="IPAddress" sql:field="IPAddress" />
sql:field="LeadSharedEMailAddress" />
</ElementType>
</
Schema>
I can execute leadsMapping.xml successfully, but once I execute b.xml I got an error that said the statement has been terminated. Why is that?
June 9, 2005 at 2:05 am
1. There is no way to update an existing record using this method. What I have found is that your bulk loading inserts everything into staging table(s) and then a sql script/stored proc is used to then insert or update the corresponding records in the live tables. You can fire this off in your script as soon as the bulk load finishes.
2. I'm not sure I understand your setup correctly but the call to objBL.Execute should take two parameters, one is your data file (xml) and the other should be your schema file (xsd). It sounds as though you may need two schemas one for each source data file. You can then specify different tables for different types/names of elements in your schema file and load the two tables at the same time. If you need/want an example of one I am currently using to do just this then I can pass it on.
Also what is the error message in C:\error.xml ?
June 9, 2005 at 8:18 am
noggin, thanx for the reply!
The error message just simply "the statement is terminated". Anyhow, I got it working yesterday. I found out that one column contains duplicated values when I tried to bulk insert to the table, that is why the error occured.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply