Is it possible to get SQL Server 2008 to download a file from a URL
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.