Looping through XMLs in folder using openrowset / "Must declare the scalar variable" Issue

Question

Essentially I am trying to use SQL to query XML files within a folder. The code to query the XML works fine on its own but once I turned it into a string to allow for expressions in 'openrowset' it keeps saying throwing out an error message saying it cannot find @x and that 'Must declare the scalar variable'. I understand there is also sp_executesql but I don't really understand how that works and why it would be needed.

Thanks in advance!

drop table #tmp

CREATE TABLE #tmp(files VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B "C:\Users\USER\A\B"';


While (Select Count(*) From #tmp where files is not null) > 0
Begin

DECLARE @fileName varchar(max)
DECLARE @filepath varchar(max)
Declare @sql nvarchar(max)

Select Top 1 @fileName = files From #tmp

Set @filepath = 'C:\Users\USER\990\A\B\' + @filename

DECLARE @n1 varchar(max)
DECLARE @n2 varchar(max)

Set @n1 = 'http://www.namespace1.com/file'
Set @n2 ='http://www.namespac2.com/XMLSchema'



SET @sql = '
DECLARE @x xml

@x = R
FROM Openrowset(Bulk ''' + @filepath + ''' , Single_Blob) AS ReturnData(R);
WITH XMLNAMESPACES(DEFAULT '''+@n1+'''
                          ,'''+@n2+''' AS xsi)

INSERT INTO [dbo].[IRS]
           ([A]
           ,[B]
           ,[C]
           )
     Select
       ct.value('''+'(../../Data/A/)[1]'+''','''+'varchar(max)'+''')
      ,ct.value('''+'(../../Data/B)[1]'+''','''+'varchar(max)'+''') 
      ,ct.value('''+'(../../Data/C/)[1]'+''','''+'varchar(max)'+''') 

FROM @x.nodes('''+'/Return/ReturnData/IRS990ScheduleH'+''') AS A(ct)
'

exec(@sql)
Delete from #tmp Where files = @FileName

End

Answer 1

I think your error happens here:

DECLARE @x xml

@x = Something

Either use

DECLARE @x XML=SomeXml;
or
DECLARE @x XML=(SELECT SomeXml FROM SomeWhere);

or

DECLARE @x xml;

and then either

SET @x=SomeXml;
or
SET @x=(SELECT SomeXml FROM SomeWhere);
or
SELECT @x=SomeXml FROM SomeWhere;