Optimal way of loading, registering, indexing and querying a huge XMLDB

Use an external process that does the HTTP work and the inserts into the database. I explicitly advise against using SQLCLR for this. Hijacking precious SQL Server workers for the boring job of waiting for HTTP results will one day impact your server severely.

but the size of the XML can be quite variable (esp given that a binary would pass it to the stored proc using L2S) and Iv'e already overflowed the input to the SP a couple of times because there's been too much data

Use the techniques from DOWNLOAD AND UPLOAD IMAGES FROM SQL SERVER VIA ASP.NET MVC to stream the HTTP response into the database.

Use execute immediate to make creation of many very similar tables easier.

A little PL/SQL demo:

SQL> 
declare 
table_number number;
ctas_sql varchar(4000);
begin
  ctas_sql:='select sysdate as foo from dual';

  for table_number in 1..10
  loop
    execute immediate('create table foo'||table_number ||' as ('||ctas_sql||')');
  end loop;
end;
/
SQL> 
PL/SQL procedure successfully completed.

SQL> 
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
FOO1
FOO2
FOO3
FOO4
FOO5
FOO6
FOO7
FOO8
FOO9
FOO10

10 rows selected.

SQL> 

The create table statement can contain anything you want - the above is just showing that the statement can be dynamic. So, in your case you'd have simple logic to alter the TEAM_ID and table name for each statement.

Bind variables will offer no gain unless you're executing the same single query multiple times (extremely simplified for the sake of this answer) - in this case you're executing many different queries that are performing large bulk inserts.

Creating tables with NOLOGGING is not advisable if this is a production system. Also be aware that not logging and /*+APPEND*/ can have backup/restore implications. This Ask Tom thread may be of particular interest.

To clarify, using CTAS is the way to go. You only have to parse the CTAS statement once to insert your millions of rows. If you use individual INSERT statements you have to parse each statement over & over again, which adds a massive overhead!