Stored Procedure to Import XML into SQL Server-CodePudding

I'm trying to import XMLs into SQL Server. If I print the below @command, I can copy and paste into SSMS and the code runs perfectly. However, I get the following error if I try to run this as an sp.

exec etl.import_xml 'c:\xml_file.xml'

Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@stream".

ALTER PROCEDURE etl.import_xml (
    @file_name      VARCHAR(1000)
) AS 
BEGIN       
    DECLARE @command NVARCHAR(1000)
    DECLARE @stream VARCHAR(MAX)
    SET @command = N'SELECT @stream = CAST(BulkColumn AS VARCHAR(MAX))
                    FROM OPENROWSET(BULK '''   @file_name   ''', SINGLE_BLOB) AS x;'
    --EXEC sp_executesql @command, N'@file_stream_out VARCHAR(MAX) OUTPUT', @stream_out = @stream OUTPUT
    EXECUTE(@command)
    SET @stream = dbo.strip_xmlns(@stream)  

    UPDATE swf.etl.raw_data
    SET xml_string = CONVERT(XML, @stream)
    WHERE xml_file_name = @file_name
END

CodePudding user response：

As per Dale K, I need to declare everything within the command string. Obviously there are still issues with SQL injection, however no user will have access to this proc and will only be run on an automation server.

ALTER PROCEDURE etl.import_xml (
    @file_name      VARCHAR(1000)
) AS 
BEGIN       
    DECLARE @command NVARCHAR(1000)
    DECLARE @stream VARCHAR(MAX)

    SET @command = N'DECLARE @stream VARCHAR(MAX) SELECT @stream = CAST(BulkColumn AS VARCHAR(MAX))
                    FROM OPENROWSET(BULK '''   @file_name   ''', SINGLE_BLOB) AS x;
                    SET @stream = dbo.strip_xmlns(@stream)  
                    UPDATE swf.etl.raw_data
                    SET xml_string = CONVERT(XML, @stream)
                    WHERE xml_file_name = '''   @file_name   '''
                    '
    EXECUTE(@command)


END

CodePudding user response：

Here is how to do it.

SQL

DECLARE @xml XML
   , @sql NVARCHAR(MAX)
   , @fileName VARCHAR(256) = 'e:\Temp\Diego.xml';

SET @sql = N'SELECT @xmlOut = XmlDoc FROM OPENROWSET (BULK '   QUOTENAME(@fileName,NCHAR(39))   ', SINGLE_BLOB) AS Tab(XmlDoc)';

EXEC sys.sp_executesql @sql, N'@xmlOut XML OUTPUT', @xmlOut = @xml OUTPUT;

SELECT @xml;