Showing posts with label doc. Show all posts
Showing posts with label doc. Show all posts

Sunday, March 11, 2012

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

Friday, February 10, 2012

Build a xml doc from parameters passed to sp

I have a sp with some input parameters and i would like to build an xml document with the elements name of the parameters with the proper value.

ie:

declare @.par2 datetime;

set @.par2=getdate();

exec spTest 'test',@.par2

create procedure spTest(@.par1 nvarchar(50),@.Par2 datetime)

as

...do some job

i would like to obtain:

<spTest><par1>test</par1><Par2>10/20/2006 10:00:00</Par2><spTest>

or, btw the same result i could get querying with the forxml clause.

I need to create this Parameter/ParametersValues to xml in many sp that have different parameters.

Thank you very much

Maybe something like one of these?

Code Snippet

declare @.par1 varchar (50) set @.par1 = 'test'
declare @.par2 datetime set @.par2 = getdate()

select '<spTest><par1>' +
case when @.par1 is null then '' else @.par1 end +
'</par1><par2>' +
case when @.par1 is null then ''
else convert(varchar(23), @.par2, 121)
end + '</par2></spTest>'
as xmlResult

/*
xmlResult
-
<spTest><par1>test</par1><par2>2007-06-02 10:08:52.327</par2></spTest>
*/

select parm as [data()]
from ( select case when @.par1 is null then ''
else @.par1
end as Parm
union all
select case when @.par2 is null then ''
else convert(varchar(23), @.par2, 121)
end
) a
for xml path ('parm'), root('spTest')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-
<spTest><parm>test</parm><parm>2007-06-02 10:16:56.187</parm></spTest>
*/

Build a xml doc from parameters passed to sp

I have a sp with some input parameters and i would like to build an xml document with the elements name of the parameters with the proper value.

ie:

declare @.par2 datetime;

set @.par2=getdate();

exec spTest 'test',@.par2

create procedure spTest(@.par1 nvarchar(50),@.Par2 datetime)

as

...do some job

i would like to obtain:

<spTest><par1>test</par1><Par2>10/20/2006 10:00:00</Par2><spTest>

or, btw the same result i could get querying with the forxml clause.

I need to create this Parameter/ParametersValues to xml in many sp that have different parameters.

Thank you very much

Maybe something like one of these?

Code Snippet

declare @.par1 varchar (50) set @.par1 = 'test'
declare @.par2 datetime set @.par2 = getdate()

select '<spTest><par1>' +
case when @.par1 is null then '' else @.par1 end +
'</par1><par2>' +
case when @.par1 is null then ''
else convert(varchar(23), @.par2, 121)
end + '</par2></spTest>'
as xmlResult

/*
xmlResult
-
<spTest><par1>test</par1><par2>2007-06-02 10:08:52.327</par2></spTest>
*/

select parm as [data()]
from ( select case when @.par1 is null then ''
else @.par1
end as Parm
union all
select case when @.par2 is null then ''
else convert(varchar(23), @.par2, 121)
end
) a
for xml path ('parm'), root('spTest')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-
<spTest><parm>test</parm><parm>2007-06-02 10:16:56.187</parm></spTest>
*/