BULK xml file using a path parameter

[Bassem]

Hi,

I'm bulking an xml file using BULK statement to an XML variable, like this:

DECLARE @xmlDoc XML
SET @xmlDoc = (
  SELECT * FROM OPENROWSET (
    BULK 'D:\root.xml', SINGLE_CLOB
  ) AS xmlData
)
SELECT @xmlDoc

I'm trying to set 'D:\root.xml' to be a paramater @filepath. But I'm getting a parsing problem, that it expects to be string or Text-LEX..!!

Here is what I did:

(
@filepath nvarchar(255)
)
SET @xmlDoc = (
  SELECT * FROM OPENROWSET (
    BULK @filepath, SINGLE_CLOB
  ) AS xmlData
)
SELECT @xmlDoc

Thanks in advanced!

[Bassem]

Hi,

After a massive searching I found a perfect solution:

http://www.sqlservercentral.com/Forums/Topic923475-338-1.aspx

DECLARE @results table (result xml)
--Build the Dynamic SQL Statement to get the data from the xml file
SET @sqlstmt= 'SELECT * FROM OPENROWSET ( BULK ''' + @filename + ''', SINGLE_CLOB )AS xmlData'

-- Insert the results of the dynamic SQL Statement into the temporary table variable.
INSERT INTO @results EXEC (@sqlstmt)

DECLARE @xmlDoc XML
SELECT @xmlDoc = result FROM @results

Thanks