Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 xml modify insert добавить идентификатор  [new]
alol55
Member

Откуда:
Сообщений: 26
Здравствуйте, помогите, пожалуйста, разобраться с задачей.
Есть вот такой xml:

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;


Идентификатор добавляется правильно. Теперь тот же метод применяю к периодам работы, но мне надо, чтобы для каждого работника нумерация периодов начиналась с 1. При таком коде она получается сквозной, то есть неправильной. Подскажите, плиз, как код исправить.

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;


И заодно еще один вопрос: можно ли такую модификацию выполнить без циклов? На маленьких xml это работает быстро, но на больших (200-300 тыс. записей), боюсь, будет тормозить.
2 фев 18, 18:29    [21161603]     Ответить | Цитировать Сообщить модератору
 Re: xml modify insert добавить идентификатор  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
alol55,

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('СТАЖ')


Думаю можно и попроще если сконвертировать с реляционный вид и потом в обратную собрать результирующую xml. Но в пятницу сильно напрягаться не хочется, уж извиняйте
2 фев 18, 21:15    [21161955]     Ответить | Цитировать Сообщить модератору
 Re: xml modify insert добавить идентификатор  [new]
alol55
Member

Откуда:
Сообщений: 26
felix_ff,

с ума сойти, это работает)
Спасибо вам огромное!!
3 фев 18, 07:09    [21162390]     Ответить | Цитировать Сообщить модератору
 Re: xml modify insert добавить идентификатор  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
alol55,

Как и писал felix_ff, можно гораздо проще и без modify:
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;
3 фев 18, 10:47    [21162534]     Ответить | Цитировать Сообщить модератору
 Re: xml modify insert добавить идентификатор  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
alol55,

вариант предложенный invm сильно лучше моего берите его за основу.
3 фев 18, 18:20    [21163159]     Ответить | Цитировать Сообщить модератору
 Re: xml modify insert добавить идентификатор  [new]
alol55
Member

Откуда:
Сообщений: 26
invm,

вам тоже спасибо огромное)
Буду пробовать оба варианта на больших xml, какой сработает быстрее, тот и возьму
4 фев 18, 17:36    [21164576]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить