--yogesh.mehla@gmail.com
--Reading xml file using OPENROWSET
SELECT cast(BulkColumn AS xml) FROM OPENROWSET(  
   BULK 'C:\xml\Sample.xml',  
   SINGLE_BLOB) AS x;  

--Reading xml file using OPENROWSET from dynamic path
DECLARE @Path VARCHAR(100)='C:\xml\Sample.xml'
DECLARE @SQL VARCHAR(100)='SELECT cast(BulkColumn AS xml) FROM OPENROWSET(  
   BULK ' + CHAR(39) + @Path + CHAR(39) + ',  
   SINGLE_BLOB) AS x'
EXEC(@SQL)

--Reading xml file using OPENROWSET from dynamic path with OUTPUT variable
DECLARE @XML XML
DECLARE @Path VARCHAR(100)='C:\xml\Sample.xml'
DECLARE @SQL NVARCHAR(1000)=N'SELECT @xml=cast(BulkColumn AS xml) FROM OPENROWSET(  
   BULK ' + CHAR(39) + @Path + CHAR(39) + ',  
   SINGLE_BLOB) AS x'
exec sp_ExecuteSQL @SQL,N'@XML xml OUTPUT',@XML OUTPUT
select @XML