Import from XML File to SQL Table

Hi ,

I am importing data from an xml file that validates itself against a schema. I need to import some of the values to the corresponding columns in the table. Other elements will be inserted to the a column of type XML.

The size of the file is about 500+MB. I have already tried SSIS. It does not validate my schema, in spite of the fact that the schema is valid. I tried to copy using SQLXML Bulk Insert, but this does not suit to my particular situation.

I am using SQL Server 2005.

What are my options? Any examples / code samples will do.

Thank you,

Zaboo

What is it about SQLXML that doesn't suit your needs?

How about some sample data, ddl, etc.?

import data and get unique id

I am trying to import a text file and have sql give it a unique id number but it keeps saying that the id field cannot have null values. What do I need to do to get this to work?So the table you are importing to does it have a column defined with IDENTITY property? What are you using to import DTS, BCP, BULK INSERT?|||I am using DTS and the table does have a identity field.|||Well that is what you will have to do first. Create your table with a column having the property of IDENTITY.

Example:CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)

See Books online for more info.|||I did that but I still get an error saying that column cannot contain a null value when I import the data for the other column out of a text file (csv).|||I can offer a different perspective,
You just have to disable the 'enable identity insert' checkbox when you are doing your import.|||That worked perfectly, Thank you so much.

Import CSV Files to SQL Server Null Values Problem

Hello,
I am trying to import a CSV file into my SQL Server database, this file was originally generated by another database table (on another server) with the same structure, the table contains two columns ofrealdatatype withAllow Null Valuesetto true for those columns, the CSV file contains the valueNULLfor theses columns, I am facing a problem when importing this file. This may be because DTS tries to represent values as strings then to convert them torealdatatype which results in transforming the value "NULL" toreal,I receive an error message saying.
Error during Transformation 'DirectCopyXform' for Row number 1. Errors encountered so far in this task: 1.

TransformCopy 'DirectCopyXform' conversion error: Conversion invalid for datatypes on column pair 8 (source column 'Col008' (DBTYPE_STR), destination column 'zip_longitude' (DBTYPE_R4)).

TransformCopy 'DirectCopyXform' conversion error: Conversion invalid for datatypes on column pair 7 (source column 'Col007' (DBTYPE_STR), destination column 'zip_latitude' (DBTYPE_R4)).
How can I work around this problem?
Any help would be appreciable

Move the data into temp table then destination and I think you should use Float instead of Real because most of the T-SQL functions dealing with Longitude and Latitude are in Float. And yes Float is the synonym of Real but if you use Float you avoid the cast to Real. Hope this helps.|||

Did you get a solution? I have the same issue.... Please let me know at the earliest...