Re: Importing XML Data 2 SQL SERVER

From: Irwin Dolobowsky [MS] (irwind_at_mail.microsoft.com)
Date: 12/16/04 

Date: Thu, 16 Dec 2004 13:13:59 -0800

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
>

Relevant Pages

  • Re: Task Manager Question
    ... Do you mean that the tabs are missing? ... outside border to bring them back. ... MS-MVP Windows Shell/User ... How to get rid of it? ...
    (microsoft.public.windowsxp.basics)
  • Re: Add Akers to the list
    ... The guy hits the upright, missing a 57 yard attempt, and you want to get rid of him? ...
    (alt.sports.football.pro.phila-eagles)
  • Re: Tasks: Assigned To list is incorrect--Help?
    ... The person who was missing from the list had Web ... WSS is on a FE web server ... > rid of the person who is not even a member of the site. ...
    (microsoft.public.sharepoint.windowsservices)
  • Missing Unicows.dll?
    ... >Any help would be tops:) ... Hi have u since posting this comment, ... the missing UNICOWS.DLL. ... and do not know how to get rid of it! ...
    (microsoft.public.windowsxp.messenger)