September 8, 2010 at 12:34 am
Hi everyone,
I am writing a stored procedure that updates a field in a SQL table with a value obtained from a XML file. The update works fine; however I'm using the OPENROWSET(BULK filename) to read the contents into a XML variable.
Is it possible to pass the filename to OPENROWSET via a stored procedure variable? Code follows:
CREATE PROCEDURE procedure_name
(
@XML_FILE NVARCHAR(100)
)
AS
-- Setup XML variable
DECLARE @xml XML
-- Read the XML file into the XML variable. It will be read into a long text string.
DECLARE @XML_FILE_NAME NVARCHAR(100)
SET @XML_FILE_NAME = @XML_FILE
SELECT @xml = BulkColumn
FROM OPENROWSET
(
BULK @XML_FILE_NAME, SINGLE_BLOB
) ROW_SET
This code generates error "Incorrect syntax near '@XML_FILE_NAME'." It works if I hard-code the file name, but I'd prefer not do to this, because this functionality will have to be re-used, and I'd prefer not to have to create a new stored procedure for each process. But if I have to, I have to.
September 8, 2010 at 12:51 am
As you’ve just found out, openrowset function doesn’t accept variables as a parameter. This behavior is documented in BOL (Books on line). You could modify your code to work with dynamic SQL (but remember that this approach has some security problems)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 8, 2010 at 6:56 pm
Hi Adi,
You confirmed what I thought. I even managed to find the bit in BOL that explains the behaviour. I had looked, but I missed that part.
Anyways, here is my inline code that does what I need it to do, in case anyone ever needs to do something similar:
CREATE PROCEDURE procedure_name
(
@XML_FILE NVARCHAR(MAX)
)
AS
-- Setup XML variable to be used to hold contents of XML file.
DECLARE @xml XML
/* Read the XML file into the XML variable. This is done via a bulk insert using the OPENROWSET()
function. Because this stored proc is to be re-used with different XML files, ideally you want to pass
the XML file path as a variable. However, because the OPENROWSET() function won't accept
variables as a parameter, the command needs to be built as a string and then passed to the
sp_executesql system stored procedure. The results are then passed back by an output variable.
*/
-- The command line
DECLARE @COMMAND NVARCHAR(MAX)
-- The definition of the parameters used within the command line
DECLARE @PARAM_DEF NVARCHAR(500)
-- The parameter used to pass the file name into the command
DECLARE @FILEVAR NVARCHAR(MAX)
-- The output variable that holds the results of the OPENROWSET()
DECLARE @XML_OUT XML
SET @FILEVAR = @XML_FILE
SET @PARAM_DEF = N'@XML_FILE NVARCHAR(MAX), @XML_OUT XML OUTPUT'
SET @COMMAND = N'SELECT @XML_OUT = BulkColumn FROM OPENROWSET(BULK ''' + @XML_FILE + ''', SINGLE_BLOB) ROW_SET';
EXEC sp_executesql @COMMAND, @PARAM_DEF, @XML_FILE = @FILEVAR,@XML_OUT = @xml OUTPUT;
--SELECT @xml
October 25, 2010 at 9:23 am
Thanks Christine, very helpful 🙂
November 26, 2010 at 4:56 am
Hi - I'm a newby to Stored Procedures and trying to tackle this problem..... :ermm:
I have a stored procedure that functions fine with a hard coded filename.
I am struggling implementing the suggested solution (posted above) for my particular procedure. I want to pass a file name to the procedure via the @pfile_name parameter
I wondered if anyone could help/point me in the right direction.
The SQL I am executing is as follows:
INSERT INTO Transactions (authAmountString, tel, countryMatch, M_course, rawAuthCode, callbackPW, M_seed, amountString, transStatus, authCost, amount, installation, countryString, testMode, M_invoice_no, M_total, fax, rawAuthMessage, M_paymentType, AVS, authAmount, postcode, cost, cardType, authCurrency, country, M_transDate, email, msgType, authMode, M_postback, M_telephone, [_SP.charEnc], M_inv_amount, desc1, M_reason, cartId, lang, currency, M_name, M_apar_id, name1, transTime, ipAddress, instId, compName, charenc, address1, authentication1, transId, M_email)
SELECT
X.payment.query('authAmountString').value('.', 'nvarchar(50)'),
X.payment.query('tel').value('.', 'nvarchar(50)'),
X.payment.query('countryMatch').value('.', 'nvarchar(10)'),
X.payment.query('M_course').value('.', 'nvarchar(50)'),
X.payment.query('rawAuthCode').value('.', 'nvarchar(50)'),
X.payment.query('callbackPW').value('.', 'nvarchar(50)'),
X.payment.query('M_seed').value('.', 'nvarchar(50)'),
X.payment.query('amountString').value('.', 'nvarchar(50)'),
X.payment.query('transStatus').value('.', 'nvarchar(50)'),
X.payment.query('authCost').value('.', 'decimal(18, 2)'),
X.payment.query('amount').value('.', 'decimal(18, 2)'),
X.payment.query('installation').value('.', 'nvarchar(50)'),
X.payment.query('countryString').value('.', 'nvarchar(50)'),
X.payment.query('testMode').value('.', 'nvarchar(50)'),
X.payment.query('M_invoice_no').value('.', 'nvarchar(50)'),
X.payment.query('M_total').value('.', 'decimal(18, 2)'),
X.payment.query('fax').value('.', 'nvarchar(50)'),
X.payment.query('rawAuthMessage').value('.', 'nvarchar(50)'),
X.payment.query('M_paymentType').value('.', 'nvarchar(50)'),
X.payment.query('AVS').value('.', 'nvarchar(50)'),
X.payment.query('authAmount').value('.', 'decimal(18, 2)'),
X.payment.query('postcode').value('.', 'nvarchar(50)'),
X.payment.query('cost').value('.', 'decimal(18, 2)'),
X.payment.query('cardType').value('.', 'nvarchar(50)'),
X.payment.query('authCurrency').value('.', 'nvarchar(50)'),
X.payment.query('country').value('.', 'nvarchar(50)'),
X.payment.query('M_transDate').value('.', 'datetime'),
X.payment.query('email').value('.', 'nvarchar(100)'),
X.payment.query('msgType').value('.', 'nvarchar(50)'),
X.payment.query('authMode').value('.', 'nvarchar(50)'),
X.payment.query('M_postback').value('.', 'nvarchar(50)'),
X.payment.query('M_telephone').value('.', 'nvarchar(50)'),
'UTF-8',
X.payment.query('M_inv_amount').value('.', 'decimal(18, 2)'),
X.payment.query('desc').value('.', 'nvarchar(100)'),
X.payment.query('M_reason').value('.', 'nvarchar(50)'),
X.payment.query('cartId').value('.', 'nvarchar(50)'),
X.payment.query('lang').value('.', 'nvarchar(50)'),
X.payment.query('currency').value('.', 'nvarchar(50)'),
X.payment.query('M_name').value('.', 'nvarchar(50)'),
X.payment.query('M_apar_id').value('.', 'nvarchar(50)'),
X.payment.query('name').value('.', 'nvarchar(50)'),
X.payment.query('transTime').value('.', 'bigint'),
X.payment.query('ipAddress').value('.', 'nvarchar(50)'),
X.payment.query('instId').value('.', 'nvarchar(50)'),
X.payment.query('compName').value('.', 'nvarchar(50)'),
X.payment.query('charenc').value('.', 'nvarchar(50)'),
X.payment.query('address').value('.', 'nvarchar(100)'),
X.payment.query('authentication').value('.', 'nvarchar(50)'),
X.payment.query('transId').value('.', 'bigint'),
X.payment.query('M_email').value('.', 'nvarchar(100)')
FROM (SELECT CAST(x AS XML)
FROM OPENROWSET(BULK @pfile_name, SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY x.nodes('payment') AS X(payment)
Many thanks,
Jim.
September 18, 2015 at 4:40 am
Perfect solution by cristina! Many thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply