DECLARE @x xml; 
SET @x = 
'<СТАЖ> 
                <Работник> 
                        <ФИО> 
                                <Фамилия>ИВАНОВ</Фамилия> 
                                <Имя>ИВАН</Имя> 
                                <Отчество>ИВАНОВИЧ</Отчество> 
                        </ФИО>                 
                        <Работа> 
                                <Период> 
                                        <С>2017-01-01</С> 
                                        <По>2017-09-10</По> 
                                </Период> 
                        </Работа> 
                        <Работа> 
                                <Период> 
                                        <С>2017-09-11</С> 
                                        <По>2017-10-08</По> 
                                </Период> 
                        </Работа> 
                </Работник> 
                <Работник> 
                        <ФИО> 
                                <Фамилия>ИВАНОВА</Фамилия> 
                                <Имя>МАРИЯ</Имя> 
                                <Отчество>СЕРГЕЕВНА</Отчество> 
                        </ФИО> 
                        <Работа> 
                                <Период> 
                                        <С>2017-01-01</С> 
                                        <По>2017-08-06</По> 
                                </Период> 
                        </Работа> 
                        <Работа> 
                                <Период> 
                                        <С>2017-08-07</С> 
                                        <По>2017-08-20</По> 
                                </Период> 
                        </Работа> 
                </Работник> 
</СТАЖ> 
'; 

declare @p int = 1, @c int; 
declare @str xml 

select @c = @x.query('count(//СТАЖ/Работник)').value('.', 'int'); 
while @p <= @c 
 begin 
   select @str='<Номер>'+cast(@p as varchar(2))+'</Номер>' 

  set @x.modify('insert sql:variable("@str") as first into (((//СТАЖ/Работник)[position() = sql:variable("@p")])[1])'); 
  select @p += 1; 
 end; 

select @x;

set @p=1 
select @c = @x.query('count(//СТАЖ/Работник/Работа)').value('.', 'int'); 
while @p <= @c 
 begin 
   select @str='<Номер>'+cast(@p as varchar(2))+'</Номер>' 

  set @x.modify('insert sql:variable("@str") as first into (((//СТАЖ/Работник/Работа)[position() = sql:variable("@p")])[1])'); 
  select @p += 1; 
 end; 


select @x;

DECLARE @x xml; 
SET @x = 
'<СТАЖ> 
                <Работник> 
                        <ФИО> 
                                <Фамилия>ИВАНОВ</Фамилия> 
                                <Имя>ИВАН</Имя> 
                                <Отчество>ИВАНОВИЧ</Отчество> 
                        </ФИО>                 
                        <Работа> 
                                <Период> 
                                        <С>2017-01-01</С> 
                                        <По>2017-09-10</По> 
                                </Период> 
                        </Работа> 
                        <Работа> 
                                <Период> 
                                        <С>2017-09-11</С> 
                                        <По>2017-10-08</По> 
                                </Период> 
                        </Работа> 
                </Работник> 
                <Работник> 
                        <ФИО> 
                                <Фамилия>ИВАНОВА</Фамилия> 
                                <Имя>МАРИЯ</Имя> 
                                <Отчество>СЕРГЕЕВНА</Отчество> 
                        </ФИО> 
                        <Работа> 
                                <Период> 
                                        <С>2017-01-01</С> 
                                        <По>2017-08-06</По> 
                                </Период> 
                        </Работа> 
                        <Работа> 
                                <Период> 
                                        <С>2017-08-07</С> 
                                        <По>2017-08-20</По> 
                                </Период> 
                        </Работа> 
                </Работник> 
</СТАЖ> 
'; 

declare @t table (
      employee_id int,
      job_id int,
      employee_data xml,
      job_data xml
);

declare @t2 table (edata xml, jdata xml);

with Employees as (
    select row_number() over (order by (select 1)) as id,
           T.c.query('.') as xdata
    from @x.nodes('/СТАЖ/Работник') T(c)
),
Jobs as (
    select
          e.id as employee_id,
          row_number() over (partition by e.id order by (select 1)) as id,
          e.xdata as x,
          T.c.query('.') as xdata
    from Employees e
        cross apply e.xdata.nodes('/Работник/Работа') T(c)
)
insert into @t
select * from Jobs


update @t
   set job_data.modify('insert <Номер>{sql:column("job_id")}</Номер> as first into (/Работа)[1]'),
       employee_data.modify('insert <Номер>{sql:column("employee_id")}</Номер> as first into (/Работник)[1]')

update @t
   set employee_data.modify('delete (/Работник/Работа)')

;with x as (
    select row_number() over (partition by employee_id order by employee_id) as e_id,
           employee_id,
           employee_data
    from @t
)
, y as (
select x.employee_data as employee_data,
       tx.x as x
from x 
      cross apply (select job_data.query('.') from @t t where t.employee_id = x.employee_id for xml path(''), type) tx(x)
where e_id = 1
)
insert into @t2
select employee_data, x
from y

update @t2
   set edata.modify('insert sql:column("jdata") into (Работник)[1]')

select edata.query('.') from @t2 for xml path(''), root('СТАЖ')

select
 row_number() over (order by (select 1)) as [Номер],
 t.n.query('ФИО'),
 j.x as [*]
from
 @x.nodes('/СТАЖ/Работник') t(n) cross apply
 (
  select
   row_number() over (order by (select 1)) as [Номер],
   a.n.query('*')
  from
   t.n.nodes('Работа') a(n)
  for xml path('Работа'), type
 ) j(x)
for xml path('Работник'), root('СТАЖ'), type;