Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Unmapped Identity Column issue with XML Bulk Load

Author  Topic 

apwestgarth
Starting Member

10 Posts
Posted - 2006-09-28 : 06:02:16
Hi,
we are using XML Bulk Load to insert a large amount of data into a SQL 2000 database. I am having a problem with an Identity column which is in a table I am inserting into but is not mapped in the schema or in the original file.

The table structures is:

ProductDescription Table -

ProductDescriptionRowID - int - 4 - identity - No Nulls
ProductID - int - 4 - Nulls Allowed
DescriptionPhraseID - nvarcher - 10 - Nulls Allowed

Example of the XML is:

<Root>
<Products>
<Product>
<ProductID>169</ProductID>
<ProductName>Product Full Text Name</ProductName>
<ProductDescriptions>
<Description>00001082</Description>
</ProductDescriptions>
<CountryID>gbr</CountryID>
<PaintType>00000006</PaintType>
<PaintSubType>00000005</PaintSubType>
<SchemeAreas></SchemeAreas>
<ApplicationMethods>
<ApplicationMethod>A</ApplicationMethod>
<ApplicationMethod>B</ApplicationMethod>
<ApplicationMethod>R</ApplicationMethod>
</ApplicationMethods>
<Schemes>
<SchemeID>67</SchemeID>
<SchemeID>71</SchemeID>
<SchemeID>5090</SchemeID>
<SchemeID>5096</SchemeID>
<SchemeID>5106</SchemeID>
</Schemes>
<BulkCodes>
<BulkCode>CPA097</BulkCode>
<BulkCode>CPA098</BulkCode>
<BulkCode>CPA099</BulkCode>
</BulkCodes>
</Product>
</Products>
<Schemes>
<Scheme>
<SchemeID>5106</SchemeID>
<Description>Scheme Description Text</Description>
<Reference>Reference ID</Reference>
<CountryID>gbr</CountryID>
<ApplicationAreas>
<ApplicationArea>00000030</ApplicationArea>
<ApplicationArea>00000031</ApplicationArea>
</ApplicationAreas>
<ProjectTypes>
<ProjectType>N</ProjectType>
</ProjectTypes>
<Substrates>
<Substrate>00000005</Substrate>
</Substrates>
<Systems>
<System>00000002</System>
</Systems>
</Scheme>
</Schemes>
</Root>

I need to have the identity column incremented every time each Product Description is entered into the table. Currently in order to get the Bulk Load to run I am having to delete the column from the table and then add it once the bulk load has completed. How can I either edit the XSD file (pasted below) or the program code to allow this behaviour?

<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="rel_productDescription" parent="Products" parent-key="ProductID" child="ProductDescription"
child-key="ProductID" />
<sql:relationship name="rel_countryProducts" parent="Products" parent-key="ProductID" child="CountryProducts"
child-key="ProductID" />
<sql:relationship name="rel_productPaintType" parent="Products" parent-key="ProductID" child="PaintTypes"
child-key="ProductID" />
<sql:relationship name="rel_productPaintSubType" parent="Products" parent-key="ProductID" child="PaintSubTypes"
child-key="ProductID" />
<sql:relationship name="rel_productPaintTypePaintSubType" parent="PaintTypes" parent-key="ProductID"
child="PaintSubTypes" child-key="ProductID" />
<sql:relationship name="rel_productSchemeAreas" parent="Products" parent-key="ProductID" child="SchemeAreas"
child-key="ProductID" />
<sql:relationship name="rel_productApplicationMethods" parent="Products" parent-key="ProductID" child="ApplicationMethods"
child-key="ProductID" />
<sql:relationship name="rel_productSchemes" parent="Products" parent-key="ProductID" child="ProductSchemes"
child-key="ProductID" />
<sql:relationship name="rel_productBulkCodes" parent="Products" parent-key="ProductID" child="ProductBulkCodes"
child-key="ProductID" />
<sql:relationship name="rel_schemeReference" parent="Schemes" parent-key="SchemeID" child="SchemeReference"
child-key="SchemeID" />
<sql:relationship name="rel_countrySchemes" parent="Schemes" parent-key="SchemeID" child="CountrySchemes"
child-key="SchemeID" />
<sql:relationship name="rel_schemeApplicationArea" parent="Schemes" parent-key="SchemeID" child="ApplicationAreas"
child-key="SchemeID" />
<sql:relationship name="rel_schemeProjectTypes" parent="Schemes" parent-key="SchemeID" child="ProjectTypes"
child-key="SchemeID" />
<sql:relationship name="rel_schemeSubstrate" parent="Schemes" parent-key="SchemeID" child="Substrates"
child-key="SchemeID" />
<sql:relationship name="rel_schemeSystem" parent="Schemes" parent-key="SchemeID" child="Systems" child-key="SchemeID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="root" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="Products" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Product" sql:relation="Products">
<xs:complexType>
<xs:sequence>
<xs:element name="ProductID" type="xs:unsignedInt" sql:field="ProductID" />
<xs:element name="ProductName" type="xs:string" sql:field="ProductName" />
<xs:element name="ProductDescriptions" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<!--<xs:element minOccurs="0" maxOccurs="1" name="Description" type="xs:string" sql:relation="ProductDescription"
sql:relationship="rel_productDescription" sql:field="DescriptionPhraseID" />-->
<xs:element minOccurs="0" maxOccurs="unbounded" name="Description" type="xs:string" sql:relation="ProductDescription"
sql:relationship="rel_productDescription" sql:field="DescriptionPhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="CountryID" type="xs:string" sql:relation="CountryProducts" sql:relationship="rel_countryProducts"
sql:field="CountryCode" />
<xs:element name="PaintType" type="xs:string" sql:relation="PaintTypes" sql:relationship="rel_productPaintType"
sql:field="PaintTypePhraseID" />
<xs:element name="PaintSubType" type="xs:string" sql:relation="PaintSubTypes" sql:relationship="rel_productPaintSubType"
sql:field="PaintSubTypePhraseID" />
<xs:element name="SchemeAreas" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="SchemeArea" type="xs:string" sql:relation="SchemeAreas"
sql:relationship="rel_productSchemeAreas" sql:field="SchemeAreaPhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ApplicationMethods" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="ApplicationMethod" type="xs:string" sql:relation="ApplicationMethods"
sql:relationship="rel_productApplicationMethods" sql:field="ApplicationMethodPhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Schemes" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="SchemeID" type="xs:unsignedInt" sql:relation="ProductSchemes"
sql:relationship="rel_productSchemes" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="BulkCodes" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="BulkCode" type="xs:string" sql:relation="ProductBulkCodes"
sql:relationship="rel_productBulkCodes" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Schemes" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Scheme" sql:relation="Schemes">
<xs:complexType>
<xs:sequence>
<xs:element name="SchemeID" type="xs:string" sql:field="SchemeID" />
<xs:element name="Description" type="xs:string" sql:field="Description" />
<xs:element name="Reference" type="xs:string" sql:relation="SchemeReference" sql:relationship="rel_schemeReference"
sql:field="Reference" />
<xs:element name="CountryID" type="xs:string" sql:relation="CountrySchemes" sql:relationship="rel_countrySchemes"
sql:field="CountryCode" />
<xs:element name="ApplicationAreas" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="ApplicationArea" type="xs:string" sql:relation="ApplicationAreas"
sql:relationship="rel_schemeApplicationArea" sql:field="ApplicationAreaPhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="ProjectTypes" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element maxOccurs="unbounded" name="ProjectType" type="xs:string" sql:relation="ProjectTypes"
sql:relationship="rel_schemeProjectTypes" sql:field="ProjectTypePhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Substrates" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element name="Substrate" type="xs:string" sql:relation="Substrates" sql:relationship="rel_schemeSubstrate"
sql:field="SubstratePhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Systems" sql:is-constant="true">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element name="System" type="xs:string" sql:relation="Systems" sql:relationship="rel_schemeSystem"
sql:field="SystemPhraseID" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Program Code

public class SQLBulkLoadWrapper
{
public const int MAXLENGTH = 1024;

[DllImport("OLE32.DLL", EntryPoint="CreateStreamOnHGlobal")]
extern public static int CreateStreamOnHGlobal( int hGlobalMemHandle, bool fDeleteOnRelease, out UCOMIStream pOutStm);

protected SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class _bulkLoader;

public SQLBulkLoadWrapper()
{
this._bulkLoader = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
}

// set the bulk loader properties here...
public SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class BulkLoader
{
set { this._bulkLoader = value; }
get { return this._bulkLoader; }
}

public void Load(Stream source, string schemaPath)
{
UCOMIStream dataStream;
CreateStreamOnHGlobal(0, true, out dataStream);

byte[] dataBytes = new byte[MAXLENGTH];
int read = 0;

while ((read = source.Read(dataBytes, 0, MAXLENGTH)) > 0)
{
dataStream.Write(dataBytes, read, IntPtr.Zero);
}

source.Close();
dataStream.Seek(0, 0, System.IntPtr.Zero);
this._bulkLoader.Execute(schemaPath, dataStream);
}
}

Thanks for taking the time to look and help.

Andrew

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-09-28 : 12:49:37
I think you need to set the KeepIdentity property on your bulk loader class to false.

See http://sqlxml.org/faqs.aspx?faq=77
Go to Top of Page

apwestgarth
Starting Member

10 Posts
Posted - 2006-09-29 : 07:00:28
Hi I have added KeepIndentity = false; but I cannot run with this option as I also need to run with IgnoreDuplicateKeys=true; and when I try I get an exception telling me this combination is not allowed.

Anyone have any ideas how I can get round this?

Thanks

Andrew
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site