BULK INSERT HELP

Hi

I am trying to do the following

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

DECLARE @.FilePath varchar(50)

SELECT @.FilePath = 'C:/Test/Test.log'

SELECT

@.doc = c from OpenRowset (BULK + @.Filepath + , SINGLE_BLOB) as T (c)

I get the following error

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near '@.Filepath'.

I am sure this is really basic.Can some one let me know what am i doing wrong

Thanks

You will have to use dynamic sql.

declare @.doc varbinary(max)

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = c from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql, N'@.doc varbinary(max) output', @.doc

go

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||

Thanks for the reply

Cna i do something like :

declare @.doc varbinary(max)

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

declare @.hdoc int

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = c from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql, N'@.doc varbinary(max) output', @.doc

exec sp_xml_preparedocument @.hdoc OUTPUT,@.doc

Thanks

|||

Use the new XML datatype, If you use XML datatype, you need not to use the sp_xmlpreparedocuement(which uses more expensive resources & legecy com objects).

See XQuery on BOL to understand how to work with new XML data typed values. if you failed to use XML datatype / XML casting you only get xml content as binary..

Code Snippet

declare @.doc XML

declare @.FilePath nvarchar(50)

declare @.sql nvarchar(max)

declare @.hdoc int

set @.FilePath = N'C:\Test\Test.log'

set @.sql = N'select @.doc = convert(xml,c) from openrowset(bulk ''' + @.FilePath + N''', SINGLE_BLOB) as T(c)'

exec sp_executesql @.sql , N'@.doc XML OUTPUT', @.doc OUTPUT

Select @.doc

|||

DECLARE @.hdoc int

DECLARE @.doc varchar(max)

--DECLARE @.doca varchar(max)

DECLARE @.SQL nvarchar(200)

DECLARE @.Filepath varchar(50)

SET @.Filepath = 'C:\XML_Processing\Test.LOG'

SET @.SQL = N'select @.doc = c from OpenRowset(BULK''' + @.Filepath + N''', SINGLE_CLOB) as T(c)'

exec

sp_executesql @.SQL, N'@.doc varchar(max) output' ,@.doc

SELECT @.doc

print @.doc

print @.SQL

I was expecting @.doc to have the whole XML from Test,LOG but it is returning me NULL

Any ideas whats going on?

Thanks

Hemanshu