Openrow set and dynamic query

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