1

Halo All,

i have this queries :

declare @Directory [varchar](max), @FileXMLName [varchar](max), @URLFileXML 
[varchar](max), @url [varchar](max)
SET @Directory = 'C:\Users\bamba\Desktop\'
SET @FileXMLName = 'testing.xml'
SET @URLFileXML = @Directory + @FileXMLName
SET @url = '''' + @URLFileXML + ''''

INSERT INTO XMLwithOpenXML(XMLData, XMLLoadedFromFolder, rowstatus , 
ModifiedDate, ModifiedBy)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, @Directory,'TRUE', GETDATE(), 
(SELECT TOP 1 sdes.original_login_name FROM sys.dm_exec_sessions AS sdes 
ORDER BY sdes.session_id DESC)  
--FROM OPENROWSET(BULK 'C:\Users\bamba\Desktop\testing.xml', SINGLE_BLOB) AS 
x; --> this line work, but i need some variable to replace the path.
FROM OPENROWSET(BULK '''' + @URL + '''', SINGLE_BLOB) AS x; -->this can't 
work

does any one can help me with my queries? any help will appreciated. and please tell me why i can't just write :

... openrowset(bulk @url, single_blob) as x;

instead, the working queries have to be like :

... openrowset(bulk 'C:\Users\bamba\Desktop\testing.xml', single_blob)as x;

Thank you.

AgusmanPriantoro
  • 43
  • 2
  • 7
  • You cannot use a variable in this place. But you can use *dynamic SQL*. [Read this answer](https://stackoverflow.com/a/44478344/5089204). In *Part 1* you'll find a working example how to create such statements dynamically and work them down with a `CURSOR` – Shnugo Jun 23 '17 at 06:49
  • **Important** Be aware, that the target path must be reachable for the SQL-Server-User! – Shnugo Jun 23 '17 at 06:50

0 Answers0