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

Importing Text File into SQL Server Problem

Hello,
I am trying to load a text file into SQL Server but the text file seems to be in an unsual format that SQL Server is having a problem reading. I have tried the various options for delimited and fixed file formats.
Any ideas would be appreciated.

Sample of the file:
Dn DCHB
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 200508082345079999 BANNER PAGE
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 20050808234507 420
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 20050808234507 425
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 20050808234507 440
What you are getting is called character conversion, try the link below to use DTS to move the file. Hope this helps.
http://www.sqldts.comsql

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?
"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names
.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

importing Data to an existing database column from an .xsl file or .cvs file

good morning,

I want to load data that i receive everydays from my customers in .xls file format (excel) or cvs file format, to the database that i have created on this purpose. but when trying to do that whith SSIS; i got an error message .... that i can't import redudant data in my database column.

Best regards.

Can you please post the exact message?

Importing data into SQL using BCP

Hello,
I have a question regarding importing data into ms sql using BCP. Can
I use BCP to load data into my sql tables, if the database I am pulling
from is at a remote location? How would I do that?
Does the database at the remote location have to be SQL, or can it be
MySQL too?
Thanks for your input.
G-
> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
BCP can load data from a file into a SQL Server table or copy data from a
SQL Server table to a file. The table/file can be local or remote but only
Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS for
other types of databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169084293.977520.191810@.11g2000cwr.googlegro ups.com...
> Hello,
> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
> Does the database at the remote location have to be SQL, or can it be
> MySQL too?
> Thanks for your input.
> G-
>
|||Dan Guzman wrote:
> BCP can load data from a file into a SQL Server table or copy data from a
> SQL Server table to a file. The table/file can be local or remote but only
> Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS for
> other types of databases.
>
Thanks, Dan. One last question, Can I use BCP to copy data from a
remote SQL server table, directly to my SQL server table?
|||> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
BCP (and BULK INSERT) requires file in/out. DTS/SSIS can copy directly from
table to table, including non-SQL Server tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169087975.361441.131140@.38g2000cwa.googlegro ups.com...
> Dan Guzman wrote:
> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
>

Importing data into SQL using BCP

Hello,
I have a question regarding importing data into ms sql using BCP. Can
I use BCP to load data into my sql tables, if the database I am pulling
from is at a remote location? How would I do that?
Does the database at the remote location have to be SQL, or can it be
MySQL too?
Thanks for your input.
G-> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
BCP can load data from a file into a SQL Server table or copy data from a
SQL Server table to a file. The table/file can be local or remote but only
Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS for
other types of databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169084293.977520.191810@.11g2000cwr.googlegroups.com...
> Hello,
> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
> Does the database at the remote location have to be SQL, or can it be
> mysql too?
> Thanks for your input.
> G-
>|||Dan Guzman wrote:
> BCP can load data from a file into a SQL Server table or copy data from a
> SQL Server table to a file. The table/file can be local or remote but onl
y
> Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS fo
r
> other types of databases.
>
Thanks, Dan. One last question, Can I use BCP to copy data from a
remote SQL server table, directly to my SQL server table?|||> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
BCP (and BULK INSERT) requires file in/out. DTS/SSIS can copy directly from
table to table, including non-SQL Server tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169087975.361441.131140@.38g2000cwa.googlegroups.com...
> Dan Guzman wrote:
> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
>

Importing data into SQL using BCP

Hello,
I have a question regarding importing data into ms sql using BCP. Can
I use BCP to load data into my sql tables, if the database I am pulling
from is at a remote location? How would I do that?
Does the database at the remote location have to be SQL, or can it be
MySQL too?
Thanks for your input.
G-> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
BCP can load data from a file into a SQL Server table or copy data from a
SQL Server table to a file. The table/file can be local or remote but only
Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS for
other types of databases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169084293.977520.191810@.11g2000cwr.googlegroups.com...
> Hello,
> I have a question regarding importing data into ms sql using BCP. Can
> I use BCP to load data into my sql tables, if the database I am pulling
> from is at a remote location? How would I do that?
> Does the database at the remote location have to be SQL, or can it be
> MySQL too?
> Thanks for your input.
> G-
>|||Dan Guzman wrote:
> > I have a question regarding importing data into ms sql using BCP. Can
> > I use BCP to load data into my sql tables, if the database I am pulling
> > from is at a remote location? How would I do that?
> BCP can load data from a file into a SQL Server table or copy data from a
> SQL Server table to a file. The table/file can be local or remote but only
> Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS for
> other types of databases.
>
Thanks, Dan. One last question, Can I use BCP to copy data from a
remote SQL server table, directly to my SQL server table?|||> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
BCP (and BULK INSERT) requires file in/out. DTS/SSIS can copy directly from
table to table, including non-SQL Server tables.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"InterActionCRMGuru" <leadtank@.gmail.com> wrote in message
news:1169087975.361441.131140@.38g2000cwa.googlegroups.com...
> Dan Guzman wrote:
>> > I have a question regarding importing data into ms sql using BCP. Can
>> > I use BCP to load data into my sql tables, if the database I am pulling
>> > from is at a remote location? How would I do that?
>> BCP can load data from a file into a SQL Server table or copy data from a
>> SQL Server table to a file. The table/file can be local or remote but
>> only
>> Microsoft SQL Server tables are supported. You'll need to use DTS/SSIS
>> for
>> other types of databases.
> Thanks, Dan. One last question, Can I use BCP to copy data from a
> remote SQL server table, directly to my SQL server table?
>

Importing data into SQL Server 2005 Eval version

Just installed SQL Server 2005 Eval version and Management Studio does not display any Import/Export functions to load data into tables of an existing database. I thought that this feature was turned off only in Management Studio Express.

Using the Object Explorer, right-click on the database, select [Tasks], then [Import Data...] or [Export Data...]