declare @inp as xml = N'<row>
  <a>ИВАНИЧЕВ</a>
  <a>АЛЕКСАНДР</a>
  <a>ИВАНОВИЧ</a>
  <a>28.08.1973</a>
  <a />
  <a>00821000086-0000</a>
  <a />
  <a />
  <a>2.1</a>
</row>
<row>
  <a>БАБКИНА</a>
  <a>ИРИНА</a>
  <a>ИВАНОВНА</a>
  <a>22.12.1973</a>
  <a />
  <a>00821000087-0000</a>
  <a />
  <a />
  <a>2.2</a>
</row>
<row>
  <a>ГРЕЗЕВА</a>
  <a>ТАТЬЯНА</a>
  <a>ЮРЬЕВНА</a>
  <a>30.04.1981</a>
  <a />
  <a>00821000088-0000</a>
  <a />
  <a />
  <a>2.1</a>
</row>
<row>
  <a>ЗАКАР</a>
  <a>МАРИЯ</a>
  <a>ВИКТОРОВНА</a>
  <a>08.11.1971</a>
  <a />
  <a>00821000089-0001</a>
  <a />
  <a />
  <a>3.3</a>
</row>';

declare @x xml, @c int = 250000;
declare @g int, @1 varchar(100), @2 varchar(100), @3 varchar(100), @4 varchar(100), @5 varchar(100), @6 varchar(100), @7 varchar(100), @8 varchar(100);

-- миллион row
select
 @x = t.x
from
 (
  select top (@c)
   @inp as [*]
  from
   master.dbo.spt_values a cross join
   master.dbo.spt_values b
  for xml path(''), type
 ) t(x);

set statistics time on;

with s as
(
 select
  t.n.value('.', 'varchar(100)') as v,
  (row_number() over (order by 1/0) - 1) / 9 as g,
  (row_number() over (order by 1/0) - 1) % 9 as n
 from
  @x.nodes('row/a') t(n)
)
select
 @1 = max(case when n = 0 then v end),
 @2 = max(case when n = 1 then v end),
 @3 = max(case when n = 2 then v end),
 @4 = max(case when n = 3 then v end),
 @5 = max(case when n = 4 then v end),
 @6 = max(case when n = 5 then v end),
 @7 = max(case when n = 6 then v end),
 @8 = max(case when n = 7 then v end)
from
 s
group by
 g;

set statistics time off;

 SQL Server Execution Times:
   CPU time = 48578 ms,  elapsed time = 55590 ms.