Importing XML Data 2 SQL SERVER

I am importing XML data to SQL SERVER using Bulk load component.
The XML file is like the following:
<?xml version="1.0" encoding="UTF-8" ?>
<legion-event-file>
<header>
<file-id>247</file-id>
<file-generated-date-time>2004-11-21T05:00:09</file-generated-date-time>
</header>
<item>
<item-id>001</item-id>
<legion-operation-id>15091</legion-operation-id>
<legion-customer-id>42828</legion-customer-id>
<legion-user-id>43722</legion-user-id>
<username>yinoue</username>
<performed-by-username>yinoue</performed-by-username>
<performed-by-user-id>43722</performed-by-user-id>
<b-customer-id>1454103</b-customer-id>
<operation-date-time>2004-11-20T10:24:23</operation-date-time>
<preferred-email>yinoue@.bmail.com.au</preferred-email>
<activation>
<indicator>INDICATOR</indicator>
<b-contract-type-code>BNETU</b-contract-type-code>
<provided-activation-code>492189440</provided-activation-code>
</activation>
</item>
</legion-event-file>
And the XSD/XDR is like:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:complexType name="ActivationType">
<xsd:sequence>
<xsd:element name="indicator" type="xsd:string"
sql:field="activation_indicator" />
<xsd:element name="b-contract-type-code" type="xsd:string"
sql:field="b_contract_type_code" />
<xsd:element name="provided-activation-code" type="xsd:string"
sql:field="provided_activation_code" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="DeactivationType">
<xsd:sequence>
<xsd:element name="indicator" type="xsd:string"
sql:field="deactivation_indicator" />
</xsd:sequence>
</xsd:complexType>
<xsd:element name="activation" type="ActivationType"
sql:relation="activation" />
<xsd:element name="deactivation" type="DeactivationType"
sql:relation="deactivation"/>
<xsd:complexType name="ItemType">
<xsd:sequence>
<xsd:element name="item-id" type="xsd:string" sql:field="item_id" />
<xsd:element name="legion-operation-id" type="xsd:string"
sql:field="legion_operation_id" />
<xsd:element name="legion-customer-id" type="xsd:string"
sql:field="legion_customer_id" />
<xsd:element name="legion-user-id" type="xsd:string"
sql:field="legion_user_id" />
<xsd:element name="username" type="xsd:string" sql:field="username" />
<xsd:element name="performed-by-username" type="xsd:string"
sql:field="performed_by_username" />
<xsd:element name="performed-by-user-id" type="xsd:string"
sql:field="performed_by_user_id" />
<xsd:element name="b-customer-id" type="xsd:string"
sql:field="b_customer_id" />
<xsd:element name="operation-date-time" type="xsd:string"
sql:field="operation_date_time" />
<xsd:element name="preferred-email" type="xsd:string"
sql:field="preferred_email" />
<xsd:element ref="activation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="item" parent-key="item_id"
child="activation" child-key="item_id" inverse="true" />
</xsd:appinfo>
</xsd:annotation>
</xsd:element>
<xsd:element ref="deactivation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="item" parent-key="item_id"
child="deactivation" child-key="item_id" inverse="true" />
</xsd:appinfo>
</xsd:annotation>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:element name="item" type="ItemType" sql:key-fields="item_id"
sql:relation="item" />
<xsd:complexType name="HeaderType">
<xsd:sequence>
<xsd:element name="file-id" type="xsd:string" sql:field="legion_file_id" />
<xsd:element name="file-generated-date-time" type="xsd:string"
sql:field="file_generated_date_time" sql:datatype="varchar" />
</xsd:sequence>
</xsd:complexType>
<xsd:element name="header" type="HeaderType"
sql:key-fields="legion_file_id" sql:relation="legion_file"/>
<xsd:element name="legion-event-file" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="header"/>
<xsd:element ref="item">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="legion_file" parent-key="legion_file_id"
child="item" child-key="legion_file_id" inverse="true" />
</xsd:appinfo>
</xsd:annotation>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
It gets error like:
[CDATA[No data was provided for column 'legion_file_id' on table 'item', and
this column cannot contain NULL values.]]
Can anyone tell me whether I should get rid of the non-relation element like
'header'?
Thanks for any feedback
Your problem seems to be some data is missing that is required. Why would
you get rid of "header"? That might solve the problem by eliminating the
field that is missing a value.
Irwin Dolobowsky
Program Manager, SqlXml
http://msdn.microsoft.com/xml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Anson Luo" <Anson Luo@.discussions.microsoft.com> wrote in message
news:E95B527F-4D1F-4FFC-A488-D84FEA1369C4@.microsoft.com...
>I am importing XML data to SQL SERVER using Bulk load component.
> The XML file is like the following:
> <?xml version="1.0" encoding="UTF-8" ?>
> <legion-event-file>
> <header>
> <file-id>247</file-id>
> <file-generated-date-time>2004-11-21T05:00:09</file-generated-date-time>
> </header>
> <item>
> <item-id>001</item-id>
> <legion-operation-id>15091</legion-operation-id>
> <legion-customer-id>42828</legion-customer-id>
> <legion-user-id>43722</legion-user-id>
> <username>yinoue</username>
> <performed-by-username>yinoue</performed-by-username>
> <performed-by-user-id>43722</performed-by-user-id>
> <b-customer-id>1454103</b-customer-id>
> <operation-date-time>2004-11-20T10:24:23</operation-date-time>
> <preferred-email>yinoue@.bmail.com.au</preferred-email>
> <activation>
> <indicator>INDICATOR</indicator>
> <b-contract-type-code>BNETU</b-contract-type-code>
> <provided-activation-code>492189440</provided-activation-code>
> </activation>
> </item>
> </legion-event-file>
> And the XSD/XDR is like:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:complexType name="ActivationType">
> <xsd:sequence>
> <xsd:element name="indicator" type="xsd:string"
> sql:field="activation_indicator" />
> <xsd:element name="b-contract-type-code" type="xsd:string"
> sql:field="b_contract_type_code" />
> <xsd:element name="provided-activation-code" type="xsd:string"
> sql:field="provided_activation_code" />
> </xsd:sequence>
> </xsd:complexType>
> <xsd:complexType name="DeactivationType">
> <xsd:sequence>
> <xsd:element name="indicator" type="xsd:string"
> sql:field="deactivation_indicator" />
> </xsd:sequence>
> </xsd:complexType>
> <xsd:element name="activation" type="ActivationType"
> sql:relation="activation" />
> <xsd:element name="deactivation" type="DeactivationType"
> sql:relation="deactivation"/>
> <xsd:complexType name="ItemType">
> <xsd:sequence>
> <xsd:element name="item-id" type="xsd:string" sql:field="item_id" />
> <xsd:element name="legion-operation-id" type="xsd:string"
> sql:field="legion_operation_id" />
> <xsd:element name="legion-customer-id" type="xsd:string"
> sql:field="legion_customer_id" />
> <xsd:element name="legion-user-id" type="xsd:string"
> sql:field="legion_user_id" />
> <xsd:element name="username" type="xsd:string" sql:field="username" />
> <xsd:element name="performed-by-username" type="xsd:string"
> sql:field="performed_by_username" />
> <xsd:element name="performed-by-user-id" type="xsd:string"
> sql:field="performed_by_user_id" />
> <xsd:element name="b-customer-id" type="xsd:string"
> sql:field="b_customer_id" />
> <xsd:element name="operation-date-time" type="xsd:string"
> sql:field="operation_date_time" />
> <xsd:element name="preferred-email" type="xsd:string"
> sql:field="preferred_email" />
> <xsd:element ref="activation">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship parent="item" parent-key="item_id"
> child="activation" child-key="item_id" inverse="true" />
> </xsd:appinfo>
> </xsd:annotation>
> </xsd:element>
> <xsd:element ref="deactivation">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship parent="item" parent-key="item_id"
> child="deactivation" child-key="item_id" inverse="true" />
> </xsd:appinfo>
> </xsd:annotation>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> <xsd:element name="item" type="ItemType" sql:key-fields="item_id"
> sql:relation="item" />
> <xsd:complexType name="HeaderType">
> <xsd:sequence>
> <xsd:element name="file-id" type="xsd:string" sql:field="legion_file_id"
> />
> <xsd:element name="file-generated-date-time" type="xsd:string"
> sql:field="file_generated_date_time" sql:datatype="varchar" />
> </xsd:sequence>
> </xsd:complexType>
> <xsd:element name="header" type="HeaderType"
> sql:key-fields="legion_file_id" sql:relation="legion_file"/>
> <xsd:element name="legion-event-file" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="header"/>
> <xsd:element ref="item">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship parent="legion_file" parent-key="legion_file_id"
> child="item" child-key="legion_file_id" inverse="true" />
> </xsd:appinfo>
> </xsd:annotation>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> It gets error like:
> [CDATA[No data was provided for column 'legion_file_id' on table 'item',
> and
> this column cannot contain NULL values.]]
> Can anyone tell me whether I should get rid of the non-relation element
> like
> 'header'?
> Thanks for any feedback
>
sql