SQL export every row item to xml file

When you call someone rude, should you expect an actual answer? A developer with any significant experience (you) should be able to find possible solutions and evaluate their usability. As an example, searching "sql server export one row to file" finds first entry using a cursor.

Quite frankly you seem to have significant experience in different languages so I question why you chose a TSQL based solution rather than one involving whatever language that is your strong suit. But that's a very different issue. No matter - RBAR is still RBAR regardless of language / development platform.

Your code converted to a simple cursor is below. There are some things you assume and I again suggest you use a language that you are proficient in.

declare @sql varchar(500);
declare @name varchar(20);
declare c1 cursor  FAST_FORWARD for 
    select name from dbo.mytable order by name;
    
open c1;
fetch next from c1 into @name;
while @@fetch_status = 0
begin
   --print @name;

   set @sql = 'select processxml from dbo.mytable where name = ''' + @name + ''''; 
   set @sql = 'bcp "' + @sql + '" queryout "' + 'C:\Temp\sql queries\'+ @name +'.xml " -w +T'
   print @sql; 
   
   fetch next from c1 into @name;
end;

close c1;
deallocate c1;

You might need to adjust this to connect to the correct instance and database using a specific login. The documentation has cursor examples - always a good starting point. Perhaps the first thing you should try is to use BCP from a command prompt to export that column in a single specific row to a specific file to validate your assumptions and expectations first.

With just a little work you could add some additional logic to run this for multiple databases within a given instance. Doing that would require handling name collisions in the set of files generated.

Lastly, your code as is but without the reliance on the temp table with the BCP command. Note the small adjustment because of the simplified table in the fiddle.

--select ROW_NUMBER() OVER (order by processid) row_num, name, processxml 
select ROW_NUMBER() OVER (order by name) row_num, name 
   into #Process 
   from dbo.mytable
SET @MaxRowsCount = (SELECT MAX(row_num) FROM #Process)
SET @Iter = 1

WHILE @Iter <= @MaxRowsCount
BEGIN
 
   SET @fileName = (select name from #Process where row_num = +@Iter)
   SET @filePath = 'C:\Temp\sql queries\'+ @fileName +'.xml'
   SET @sqlStr = 'select processxml from dbo.mytable where row_num ='+cast(@Iter as varchar(3))
 
   SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout "' + @filePath + '" -w -T'
   print @sqlCmd;
   --EXEC xp_cmdshell @sqlCmd

   SET @Iter = @Iter + 1
END
Drop TABLE #Process

fiddle to demonstrate both.

This is really not a job for T-SQL, it is not a generalized scripting language, it is only meant for querying.

Instead, use Powershell to extract the XML as separate rows, then output them into files

Invoke-Sqlcmd
  -ServerInstance "YourServer"
  -Database "YourDB" -Username "user" -Password "pass"
  -Query "select name, processxml from dbo.mytable;"
| % {
    Out-File -FilePath ("Downloads\temp\" + $_.name + ".xml") -InputObject $_.processxml
}

You could also use any other client app to do the same, far more easily than in T-SQL.