Openrow set and dynamic query

Question

DECLARE @xmlDoc XML;
 DECLARE @XMLFileName VARCHAR(500)

DECLARE @FileName Varchar(50)

set @FileName = 'test.xml'

Following statement works with hardcoded filepath,

SET @xmlDoc =(sELECT  CONVERT(xml, BulkColumn, 2)
                 FROM OPENROWSET (BULK 'C:\ExternalSourceData\test.xml', SINGLE_BLOB)    AS xmlData   )

when i use with variable, giving incorrect syntax,

SET @XMLFileName='C:\ExternalSourceData\'+@FileName

SET @xmlDoc =(sELECT  CONVERT(xml, BulkColumn, 2)
                 FROM OPENROWSET (BULK  @XMLFileName, SINGLE_BLOB)    AS xmlData   )

How to make this  dynamic SQL and assign result it to variable?

Thanks

V

Answer 1

-- 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

Answer 2

DECLARE @xmlDoc XML , @XMLFileName VARCHAR(500)


DECLARE @FileName Varchar(256) = 'Stud.xml'
SET @XMLFileName='D:\XMLSamples\'+@FileName


--SET @xmlDoc =(sELECT  CONVERT(xml, BulkColumn, 2)
--              FROM OPENROWSET (BULK 'D:\XMLSamples\Stud.xml', SINGLE_BLOB)    AS xmlData   )


EXEC('SELECT  CONVERT(xml, BulkColumn, 2) XMLdata INTO ##temp
              FROM OPENROWSET (BULK  '''+@XMLFileName+''', SINGLE_BLOB)    AS xmlData'  ) 

SELECT * FROM ##temp

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 3

SELECT * FROM ##temp

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

This solution would NOT work if there any many concurrent users doing same activity because ##Temp is global temp table.

---

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

Answer 4

Hi Balmukund ,

Thanks for your suggestion , I agree with you . I was just trying to answer on how to form dynamic SQL .

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

DECLARE @xmlDoc XML , @XMLFileName VARCHAR(256),@SPID CHAR(5)


DECLARE @FileName Varchar(256) = 'Stud.xml'
SET @XMLFileName='D:\XMLSamples\'+@FileName


--SET @xmlDoc =(sELECT  CONVERT(xml, BulkColumn, 2)
--              FROM OPENROWSET (BULK 'D:\XMLSamples\Stud.xml', SINGLE_BLOB)    AS xmlData   )

SELECT @SPID = @@SPID

EXEC('SELECT  CONVERT(xml, BulkColumn, 2) XMLdata INTO ##temp_'+@SPID+'
              FROM OPENROWSET (BULK  '''+@XMLFileName+''', SINGLE_BLOB)    AS xmlData'  ) 

EXEC('SELECT * FROM ##temp_'+@SPID)

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 5

Hi Balmukund ,

Thanks for your suggestion , I agree with you . I was just trying to answer on how to form dynamic SQL .

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

EXEC('SELECT * FROM ##temp_'+@SPID)

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Correct Sathya. I was just trying to explain that we need to add session handling. Your new query looks perfectly fine.

---

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

Answer 6

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, [e-mail]