I am having a problem importing XML documents into SQL using SQLXML Bulkload with an annotated XSD schema. I have three, hierarchical tables in a SQL 2000 DB: tblBatches -> tblRecords -> tblDetails. The XML document I want to import does not contain identity fields. So I am using '.KeepIdentity = False' in my ActiveX VB Script, so SQL Server will generate the key fields for me. When I run the script, the message I get is 'No data was provided for column 'record_ID' on table 'tblDetails', and this column cannot contain NULL values.' So, basically, it's not inserting the matching foreign key generated for tblRecords into tblDetails.
I have been playing around with this for several days, and if I remove all reference to the third table in the hierarchy, tblDetails, from my XSD schema, the data imports and SQL successfully inserts the identity fields for me for the first two tables. If it will insert the identity fields for two tables, why is it having so much trouble doing it for three? I thought SQLXML 3.0 SP2 was supposed to fix the identity propagation problem. I am using SQLXML 3.0 SP2, Windows 2000, VS.NET 2002, and SQL Server 2000.
I am pasting my VB code, XSD schema, and XML document below. I will also attach copies of them, if that is easier.
Here is the VB code I am using to run the bulkload:
Dim BulkLoad As SQLXMLBulkLoad3
Try
'Create the new XML Bulk Load object
BulkLoad = New SQLXMLBulkLoad3()
'Set the connection string.
BulkLoad.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;User ID=mmsuser;Initial Catalog=MMS;Data Source=NDEWITT\NetSDK;"
'Log any errors.
BulkLoad.ErrorLogFile = "C:\TestSQLErrorLog.xml"
'Have it automatically add the identity fields.
BulkLoad.KeepIdentity = False
BulkLoad.CheckConstraints = True
BulkLoad.Transaction = False
'And perform the data import.
BulkLoad.Execute("C:\TestMMSSchemaNETxsd.xsd", _
"C:\TestXDRNoIdentity.xml")
'Clean up.
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
BulkLoad = Nothing
End Try
Here is my XSD schema:
<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:msch="urn:schemas-microsoft-com:mapping-schema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BatchRec" parent="tblBatches" parent-key="batch_ID" child="tblRecords" child-key="batch_ID" />
<sql:relationship name="RecDetail" parent="tblRecords" parent-key="record_ID" child="tblDetails" child-key="record_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="statement_batch" sql:relation="tblBatches" sql:key-fields="batch_ID">
<xs:complexType>
<xs:sequence>
<xs:element name="print_batch_key" type="xs:string" />
<xs:element name="print_batch_type_value" type="xs:string" />
<xs:element name="client_program_key" type="xs:string" />
<xs:element name="print_item_quantity" type="xs:int" />
<xs:element name="start_date" type="xs:date" />
<xs:element name="end_date" type="xs:date" />
<xs:element name="client_key" type="xs:string" />
<xs:element name="client_name" type="xs:string" />
<xs:element name="program_key" type="xs:string" />
<xs:element name="program_name" type="xs:string" />
<xs:element name="return_address_line_1" type="xs:string" />
<xs:element name="return_address_line_2" type="xs:string" />
<xs:element name="return_city" type="xs:string" />
<xs:element name="return_state" type="xs:string" />
<xs:element name="return_zip" type="xs:string" />
<xs:element name="miles_synonym" type="xs:string" />
<xs:element name="statement_synonym" type="xs:string" />
<xs:element name="member_id_synonym" type="xs:string" />
<xs:element name="member_synonym" type="xs:string" />
<xs:element name="redemption_http" type="xs:string" />
<xs:element name="redemption_phone" type="xs:string" />
<xs:element name="print_logo" type="xs:string" />
<xs:element name="run_date" type="xs:date" />
<xs:element name="custom_message" type="xs:string" />
<xs:element name="summary_text" type="xs:string" />
<xs:element name="format_type" type="xs:string" />
<xs:element name="envelope_type" type="xs:string" />
<xs:element name="paper_type" type="xs:string" />
<xs:element name="statement_record" sql:relation="tblRecords" sql:relationship="BatchRec" sql:key-fields="record_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="member_id" type="xs:string" />
<xs:element name="first_name" type="xs:string" />
<xs:element name="last_name" type="xs:string" />
<xs:element name="address_line_1" type="xs:string" />
<xs:element name="address_line_2" type="xs:string" />
<xs:element name="city" type="xs:string" />
<xs:element name="state" type="xs:string" />
<xs:element name="zip" type="xs:string" />
<xs:element name="country_id" type="xs:string" />
<xs:element name="employer" type="xs:string" />
<xs:element name="begin_balance" type="xs:int" />
<xs:element name="award_activity" type="xs:int" />
<xs:element name="redeem_activity" type="xs:int" />
<xs:element name="expired_miles" type="xs:int" />
<xs:element name="adjusted_miles" type="xs:int" />
<xs:element name="end_balance" type="xs:int" />
<xs:element name="unvested_balance" type="xs:int" />
<xs:element name="detail_record" sql:relation="tblDetails" sql:relationship="RecDetail" sql:key-fields="detail_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="effective_date" type="xs:date" />
<xs:element name="activity_type" type="xs:unsignedByte" />
<xs:element name="activity_description" type="xs:string" />
<xs:element name="activity_miles" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MMSSchema" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="statement_batch" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Here is a sample XML document I want to import:
<statement_batch>
<print_batch_key>609167</print_batch_key>
<print_batch_type_value>Member Statement</print_batch_type_value>
<client_program_key>857148</client_program_key>
<print_item_quantity>108</print_item_quantity>
<start_date>02-13-2004</start_date>
<end_date>03-04-2004</end_date>
<client_key>317229</client_key>
<client_name>CARLSON SYSTEMS</client_name>
<program_key>857143</program_key>
<program_name>Carlson Systems -The Green Mile</program_name>
<return_address_line_1>3475 Piedmont Rd. NE</return_address_line_1>
<return_address_line_2>Suite 300</return_address_line_2>
<return_city>Atlanta</return_city>
<return_state>GA</return_state>
<return_zip>30305</return_zip>
<miles_synonym>Points</miles_synonym>
<statement_synonym>Statement</statement_synonym>
<member_id_synonym>Participant ID</member_id_synonym>
<member_synonym>Participant</member_synonym>
<redemption_http>www.webrewards.com</redemption_http>
<redemption_phone>Merchandise- (877) 690-2080; Travel- (800) 210-8539</redemption_phone>
<print_logo>http://www.webrewards.com/carlson_sy...sonSystems.jpg</print_logo>
<run_date>03-04-2004</run_date>
<custom_message><![CDATA[
<BR>
<BR>]]></custom_message>
<summary_text>
</summary_text>
<format_type>STATEMENT_DETAIL_LOGO</format_type>
<envelope_type>PISTOL_STATEMENT</envelope_type>
<paper_type>STATEMENT_OFFSET_GRAY</paper_type>
<statement_record>
<member_id>859953</member_id>
<first_name>MICHAEL</first_name>
<last_name>STAPLES</last_name>
<address_line_1>4300 DELLWOOD LANE</address_line_1>
<address_line_2>
</address_line_2>
<city>MOUND</city>
<state>MN</state>
<zip>55364</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>313152</begin_balance>
<award_activity>21924</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>335076</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>176</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>265</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1344</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>235</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>3103</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>5435</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>154</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>66</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>500</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>597</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1014</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>756</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>56</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>490</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>212</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1875</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>256</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>240</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>202</activity_miles>
</detail_record>
</statement_record>
<statement_record>
<member_id>859954</member_id>
<first_name>M</first_name>
<last_name>LEGAULT</last_name>
<address_line_1>13156 BITTERSWEET ST NW</address_line_1>
<address_line_2>
</address_line_2>
<city>COON RAPIDS</city>
<state>MN</state>
<zip>55448</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>91188</begin_balance>
<award_activity>5569</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>96757</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>80</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>105</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>891</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1543</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
</statement_record>
</statement_batch>
Any help will be greatly appreciated. Thanks!I discovered your post at Midtown Computer Systems Enterprise web site about SQLXMLBulkLoad and identity columns
in 3 hierarchical tables. I have having similar problems as the ones you described in your post. Were you able to find
a solution? If so, can you share it here? Thanks.sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment