sql-serveropenrowsetxml-import

SQL SERVER - OPENROWSET with filepath parameter


I have this SQL script tht work properly:

INSERT INTO #XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\temp\test.wordpress.2017-05-22.xml', SINGLE_BLOB) AS x;


SELECT @XML = XMLData FROM #XMLwithOpenXML

Now I need to pass the xml file path with a variable.

How do I have to change the script?

Thanks to support


Solution

  • You can use dynamic SQL:

    create table #XMLwithOpenXML(XMLData xml, LoadedDateTime DateTime)
    
    declare @xml xml
    declare @filename nvarchar(100)
    declare @sql nvarchar(max)
    
    set  @filename ='F:\a.xml'
    set  @sql = 'INSERT INTO #XMLwithOpenXML(XMLData, LoadedDateTime) '
    set  @sql = @sql +' SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() '
    set  @sql = @sql +' FROM OPENROWSET(BULK ''' + @filename +''', SINGLE_BLOB) AS x;'
    
    EXEC (@Sql)
    
    SELECT @XML = XMLData FROM #XMLwithOpenXML
    
    SELECT @XML
    
    drop table #XMLwithOpenXML