Openrow set and dynamic query

We can insert XML data into XML variable and also with temp table , to handle global temp table , Can it be done this way :

It can, but there is zero reason to do this, and I frightend to see a fellow MVP to post such a bad solution. Please look at Balmukund's first post to see how to properly solve this with sp_executesql. No need for any temp table at all.

Here is a cleaned-up version of Balmukund's script. There were some extra variables and parameters. And most importantly, I use quotename to interpolate the file name in the string, to avoid the risk of SQL injection:

/* 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 output variable that holds the results of the OPENROWSET()
DECLARE @xml XML

SET @PARAM_DEF = N'@XML_OUT XML OUTPUT'

SET @COMMAND = N'SELECT @XML_OUT = BulkColumn FROM OPENROWSET(BULK ' +
                                quotename(@XML_FILE, '''') + ', SINGLE_BLOB) ROW_SET';

EXEC sp_executesql @COMMAND, @PARAM_DEF, @XML_OUT = @xml OUTPUT;

SELECT @xml

---

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se