sorting fixed length file in sql server

[S Palip]

hi, I'm having a fixed length file hvaing rows of data and some/all of the records can be grouped together using a rowid. for ex: xx1|uiy2jof7giu4|0012000|2007-06-29|AAX xx3|uiy1jofh3iud|0012000|2007-06-29|AAX xx4|uiygjofhgiud|0020100|2007-06-29|CUY xx1|uiyg3vb4giud|0212000|2007-06-30|1HU xx1|uiygjofhgiud|0002000|2007-06-21|ZXX xx4|ui2ygj7fhgi9|0052000|2007-07-29|559 Is it possible to read the file into sql as xmldata type and sum a particular value in the row with same id and send to the front end. Can any one help ? Thanks and Rgds, PLS

[Steven J Jowett]

I am no expert, but I believe you will need to look at DTS (Data Transformation Services)

Steve Jowett ------------------------- Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'

[Rami Said Abd Alhalim]

you want read XML file and saving on SQL server ??

[S Palip]

no the source file is a fixed length text file. the data should be read into sql server as xml datatype and sorted and a particular numeric field should be added there and returned to a dataset.

[TomGarth]

There are better methods for diong this, but maybe this will help some. *********************** declare @cmd varchar(300), @file varchar(255) set @file = 'C:\temp.txt' set @cmd = 'type ' + @file create table #temp (f_All varchar(255)) create table #temp2 (f1 varchar(10), f2 varchar(50), f3 varchar(10), f4 datetime, f5 varchar (10) ) insert into #temp EXEC master..xp_cmdshell @cmd insert into #temp2 select substring(f_all, 1, 3), substring(f_all, 5, 12), substring(f_all, 18, 7), substring(f_all, 26, 10), substring(f_all, 37, 3) from #temp where f_all is not null select * from #temp2 -- GROUP, SORT, SUMMARIZE, AND USE IT HOWEVER YOU NEED IT. drop table #temp drop table #temp2 **********************

Tom Garth Developer R. L. Nelson and Associates, Inc., Virginia