Openrow set and dynamic query

-- 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.
*/
declare @XML_FILE  NVARCHAR(MAX) 
set @XML_FILE = 'C:\kooda\XML.xml'
-- 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

---

Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker

--------------------------------------------------------------------------------

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------------------------------------------------------------------

My Blog | Team Blog | @Twitter

Author: SQL Server 2012 AlwaysOn - Paperback, Kindle