Bulk uploading a large xml file into single column in table

arul1984 · March 12th, 2009, 02:17 PM

Hi members,

I want to bulk upload a large xml file into a single column of a table. I tried the below code but varchar variable supports only upto 8000 characters and my file contents exceeds the length.Is there any way to accomplish in sql 2000?

Code:

 
DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileName = 'C:\Temp\CurrentSettings.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
SET @x = 0
WHILE @x <> @y
    BEGIN
        SET @x = @x + 1
        SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK
 = @x
    END
SELECT @FileContents as FileContents
DROP TABLE #tempXML

Thanks

Peso · March 13th, 2009, 05:15 AM

Create a staging table with only one TEXT or NTEXT column.

DECLARE @cmd NVARCHAR(4000)
SET @cmd ='BULK INSERT dbo.MyTable
FROM '+QUOTENAME(@fileName,'''')+'
WITH (
BATCHSIZE = 1,
CODEPAGE = ''ACP'',
FIELDTERMINATOR = ''0x00'',
ROWTERMINATOR = ''\n\n''
)'
EXEC (@cmd)

then update your original table with the value from the staging table.