Importing Fixed-width txt file - problem

Hi,

I was trying to import a fixed-width file to a sql 2005 table.
The total record lenght is 1500. I was trying to import it to a single column.

The strange thing that's happening is: SSIS is inserting only the first 32 chars of the record and the remaining are gone. I tried using nvarchar(max) and varchar(max) but of no use.
I think something somewhere is going wrong but I was unable to figure it out. Earlier I was able to load a similar file into a single column table.

My Header row delimiter is {CR}{LF}
The preview pane shows the complete record but when it transfers to the table, I'm getting 32 chars only.

Can anybody suggest any ideas to figure this out?

Thanks,
Siva.

Are there NULL characters in your data?|||Yes.........NULLs are there..|||You need to get rid of NULLs. NULLs are string terminators, which is likely why your data "stops" at a specific position on each record.

Your other alternative is to read in the data as binary using a script task, searching for NULLs and replacing them with spaces (or some other valid string character of your choosing). If you write this code, let me know. I need to do just this and haven't had the time to write it.

Importing Data from XML file to tables.

hi All ,

I have two tables as Table1([ColA] , [ColB]) , Table2([Col1] , [Col2]) in my DB.

And i have a single XML file which contains the data for the two tables separately.

as <Table1> +

<Table2> +

Now i have to import this data from the XML to both the Tables in one shot. Plz suggest a possible way to get this done.

Thanks in advance for the help.

you can try this proc

CREATE PROCEDURE dbo.SaveXML
(
@.XMLDoc text
)
AS
DECLARE @.idoc int
DECLARE @.SQLStatement as nvarchar(4000)
SET NOCOUNT ON

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.XMLDoc

DECLARE @.cTableName varchar(50)
DECLARE @.cXMLNode varchar(50)

DECLARE TableNamesCursor CURSOR FOR
SELECT TableName
FROM (
SELECT 'Table1' AS TableName UNION ALL
SELECT 'Table2' AS TableName
) TableNames

OPEN TableNamesCursor

FETCH NEXT FROM TableNamesCursor
INTO @.cTableName

SET @.SQLStatement = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.cXMLNode = '/' + @.cTableName

SET @.SQLStatement = @.SQLStatement + N' SELECT * INTO #tmp' + @.cTableName + ' FROM OPENXML(@.idoc,''' + @.cXMLNode + ''', 2)
WITH ' + @.cTableName + ';
INSERT INTO ' + @.cTableName + ' SELECT * FROM #tmp' + @.cTableName + ''

FETCH NEXT FROM TableNamesCursor
INTO @.cTableName
END

CLOSE TableNamesCursor
DEALLOCATE TableNamesCursor

EXECUTE sp_executesql @.SQLStatement, N'@.XMLDoc text,@.idoc int',@.XMLDoc, @.idoc

EXEC sp_xml_removedocument @.idoc

SET NOCOUNT OFF
GO|||

thanks for the quick reply Rhamille .

As in ur proc u r passing the XML as parameter.

But my prob is i have xml file saved at location c:\MyXML.xml .

How can i retrieve the required XML contents from this saved file .

Also the table names in DB are Table1 & Table2 . And the format of the XML file is something like this :

-<Report .......... >

- <table1>

- <Data>

<Record col1 = "A" col2 = "B" / >

<Record col1 = "C" col2 = "D" / >

<Record col1 = "E" col2 = "F" / >

</Data>

</table1>

- <table2>

- <Data>

<Record col1 = "A" col2 = "B" / >

<Record col1 = "C" col2 = "D" / >

<Record col1 = "E" col2 = "F" / >

</Data>

</table2>

</Report>

Plz suggest the necessary changes in the SP so that this can be achived.

|||

Here it is...(i used your xml as sample xml file)

Code Snippet

Create Table #Content

(

Lines Nvarchar(4000)

);

Insert Into #Content

Exec master..xp_cmdshell 'Type C:\data.xml' --Change the XML File Path

Declare @.xmlas NVarchar(4000);

Declare @.idoc as Int;

Set @.xml = ''

Select @.xml = @.xml + Ltrim(Rtrim(Lines)) From #Content Where Lines is NOT NULL

Select @.xml

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml

--First table

Select

*

From

OpenXML(@.idoc, '/Report/table1/Data/Record',1)

With

(

col1 varchar(100),

col2 varchar(100)

);

--Second table

Select

*

From

OpenXML(@.idoc, '/Report/table2/Data/Record',1)

With

(

col1 varchar(100),

col2 varchar(100)

);

EXEC sp_xml_removedocument @.idoc

|||here's the updated sp from my previous post

CREATE PROCEDURE dbo.SaveXML
(
@.xmlPath nvarchar(100)
)
AS

DECLARE @.idoc int
DECLARE @.SQLStatement as nvarchar(4000)
DECLARE @.XMLDoc nvarchar(4000)

SET NOCOUNT ON

CREATE TABLE #XMLTemp (rowID int IDENTITY, xmlRow nvarchar(4000))
DECLARE @.cmd nvarchar(100)
SET @.cmd = N'TYPE ' + CONVERT(nvarchar,@.xmlPath)
INSERT #XMLTemp EXEC master.dbo.xp_cmdshell @.cmd
SELECT @.XMLDoc =
CASE WHEN rowID = 1 THEN
ISNULL(RTRIM(xmlRow), '')
ELSE
@.XMLDoc + ISNULL(RTRIM(xmlRow), '')
END
FROM #XMLTemp
WHERE xmlRow IS NOT NULL
ORDER BY
rowID ASC

DROP TABLE #XMLTemp

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.XMLDoc

DECLARE @.cTableName varchar(50)
DECLARE @.cXMLNode varchar(50)

DECLARE TableNamesCursor CURSOR FOR
SELECT DestinationTables
FROM (
SELECT 'Table1' AS DestinationTables UNION ALL
SELECT 'Table2' AS DestinationTables
) XMLDumpTables

OPEN TableNamesCursor

FETCH NEXT FROM TableNamesCursor
INTO @.cTableName

SET @.SQLStatement = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.cXMLNode = '/Report/' + @.cTableName + '/Data/Record'

SET @.SQLStatement = @.SQLStatement + N' SELECT * INTO #tmp' + @.cTableName + ' FROM OPENXML(@.idoc,''' + @.cXMLNode + ''', 1)
WITH ' + @.cTableName + ';
INSERT INTO ' + @.cTableName + ' SELECT * FROM #tmp' + @.cTableName + ''

FETCH NEXT FROM TableNamesCursor
INTO @.cTableName
END

CLOSE TableNamesCursor
DEALLOCATE TableNamesCursor

EXECUTE sp_executesql @.SQLStatement, N'@.XMLDoc text,@.idoc int',@.XMLDoc, @.idoc

EXEC sp_xml_removedocument @.idoc

SET NOCOUNT OFF
GO

-- to use EXEC dbo.SaveXML 'C:\data.xml'|||

If you're using SQL Server 2005

Code Snippet

CREATE PROCEDURE dbo.LoadXML

AS

SET NOCOUNT ON

DECLARE @.xdoc xml

SELECT @.xdoc = bulkcolumn

from openrowset(bulk 'c:\myxml.xml', SINGLE_BLOB) as xdata --Your xml input file

insert into Table1

select t1.row.value('@.col1', 'char') as ColA, --Change 'char' to your datatype for each column

t1.row.value('@.col2', 'char') as ColB

from @.xdoc.nodes('/Report/table1/Data/Record') as t1(row)

insert into Table2

select t2.row.value('@.col1', 'char') as Col1,

t2.row.value('@.col2', 'char') as Col2

from @.xdoc.nodes('/Report/table2/Data/Record') as t2(row)

|||

Thanks Rhamille and Manivannan ( Mani ) for ur guidance..

Mission Accomplished !!!!

|||Thanks DaleJ for a new idea...i ll try this one too...