OpenRowSet Path As Variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

OpenRowSet Path As Variable

Author

Topic

jblah
Starting Member

11 Posts

Posted - 2008-06-30 : 16:47:48

I was wondering if anyone knows how to set up openrowset path as a variable that can be the input of a stored procedure.

BEGIN TRY
BEGIN TRAN
IF (
(@FilePath = ' ')
)
BEGIN
RAISERROR('Please Provide the File Path for the XML Document in the format
C:\location\location\xmldata.xml',11,3)
RETURN
END -- End null check

ELSE
BEGIN
-- Inputting data from a given disk location into the database
DECLARE @bankdata xml
SET @bankdata = (SELECT CAST(BulkColumn AS XML) FROM
openrowset(bulk N'@FilePath',
single_blob) AS X)

i'm trying to get this to work but it will not work because @FilePath is not a location. its giving me the following error:

Msg 4860, Level 16, State 1, Procedure LoadXMLBankData, Line 26
Cannot bulk load. The file "@FilePath" does not exist.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-30 : 16:56:33

Try with dynamic SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jblah
Starting Member

11 Posts

Posted - 2008-06-30 : 18:19:58

i don't see how that works? i'm trying ti input the @FilePath through a stored procedure. sorry i'm still relatively new to SQL. is there an example somewhere? or can you give me an example code?

here let me add more details to this

ALTER PROC LoadXMLBankData
@FilePath nvarchar(500)

BEGIN
-- Inputting data from a given disk location into the database
DECLARE @bankdata xml
SET @bankdata = (SELECT CAST(BulkColumn AS XML) FROM
openrowset(bulk N'@FilePath',
single_blob) AS X)

so as you can see i defined it as part of the stored procedure. i would like to input the location of the xml data part of the filepath call through the openrowset..or another function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 01:10:11

quote:
Originally posted by jblah

i don't see how that works? i'm trying ti input the @FilePath through a stored procedure. sorry i'm still relatively new to SQL. is there an example somewhere? or can you give me an example code?

here let me add more details to this

ALTER PROC LoadXMLBankData
@FilePath nvarchar(500)

BEGIN
-- Inputting data from a given disk location into the database
DECLARE @bankdata xml
SET @bankdata = (SELECT CAST(BulkColumn AS XML) FROM
openrowset(bulk N'@FilePath',
single_blob) AS X)

so as you can see i defined it as part of the stored procedure. i would like to input the location of the xml data part of the filepath call through the openrowset..or another function.

use dynamic sql as suggested. something like:-

SET @Sql='SELECT CAST(BulkColumn AS XML) FROM
openrowset(bulk N'''+@FilePath+''',
single_blob)'
EXEC (@Sql)

jblah
Starting Member

11 Posts

Posted - 2008-07-01 : 10:47:01

Ok i've simplified this down a whole lot. here is my current problem

ALTER PROC LoadXMLBankData
@FilePath nvarchar(500)

AS
BEGIN

DECLARE @Query AS nvarchar(max)
DECLARE @bankdata xml
SET @Query = N'SELECT @data = CAST(BulkColumn AS XML) FROM
openrowset(bulk N"' + @FilePath + '",
single_blob) AS XmlData'

EXEC sp_executesql @Query, N'@data AS xml OUTPUT', @bankdata OUTPUT
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @bankdata

SELECT * FROM
-- Add OPENXML function for Customer.CustomerInfo table INSERT
OPENXML (@hdoc, '/Bank/Customer', 1)
WITH
(
CustomerID int '@ID',
Fname nvarchar(50) '@FirstName',
Lname nvarchar(50) '@LastName',
Address1 nvarchar(50) '@Street',
City nvarchar(50) '@City',
State char(2) '@State',
Zip char(5) '@Zip',
Email nvarchar(50) '@EMail'
)

EXEC sp_xml_removedocument @hdoc

END

EXEC LoadXMLBankData
'C:\BankData'

it compiles just fine however when i try to run it i get the following error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'N'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 13:27:34

whats the varaiable @data for?what are you trying to put into it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources