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

Откуда: Moscow
Сообщений: 1837
Всем доброго времени суток!

дана таблица вида
DECLARE @tbl_units TABLE (
            rid UNIQUEIDENTIFIER,
            id UNIQUEIDENTIFIER,
            lvlid INT,
            unitid INT,
            value SQL_VARIANT,
            value_order INT,
            type INT
                         )

INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES (null, '8EA1487A-0382-4A08-87A3-E53161097D31', 1, 10001, 100, 1, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES (null, '8EA1487A-0382-4A08-87A3-E53161097D31', 1, 10001, 2000804048, 2, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES (null, '8EA1487A-0382-4A08-87A3-E53161097D31', 1, 10001, 'name', 3, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES (null, '8EA1487A-0382-4A08-87A3-E53161097D31', 1, 10001, 0, 0, 1)

INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('8EA1487A-0382-4A08-87A3-E53161097D31', 'A22D917C-D766-4698-80E0-57F754D1715D', 2, 10122, 'surname', 1, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('8EA1487A-0382-4A08-87A3-E53161097D31', 'A22D917C-D766-4698-80E0-57F754D1715D', 2, 10122, 'name', 2, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('8EA1487A-0382-4A08-87A3-E53161097D31', 'A22D917C-D766-4698-80E0-57F754D1715D', 2, 10122, 'midname', 3, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('8EA1487A-0382-4A08-87A3-E53161097D31', 'A22D917C-D766-4698-80E0-57F754D1715D', 2, 10122, 0, 0, 1)

INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('A22D917C-D766-4698-80E0-57F754D1715D', 'B1D00795-FB48-4ED5-AE32-DEF108F878B0', 3, 10125, 'surname', 1, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('A22D917C-D766-4698-80E0-57F754D1715D', 'B1D00795-FB48-4ED5-AE32-DEF108F878B0', 3, 10125, 'name', 2, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('A22D917C-D766-4698-80E0-57F754D1715D', 'B1D00795-FB48-4ED5-AE32-DEF108F878B0', 3, 10125, 'midname', 3, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('A22D917C-D766-4698-80E0-57F754D1715D', 'B1D00795-FB48-4ED5-AE32-DEF108F878B0', 3, 10125, 0, 0, 1)

INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('A22D917C-D766-4698-80E0-57F754D1715D', '68E7C813-765F-4F9A-AF1B-F5A4CD467C8B', 3, 10500, 2005, 1, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('A22D917C-D766-4698-80E0-57F754D1715D', '68E7C813-765F-4F9A-AF1B-F5A4CD467C8B', 3, 10500, 2000, 2, 0)
INSERT INTO @tbl_units (rid, id, lvlid, unitid, value, value_order, type) VALUES ('A22D917C-D766-4698-80E0-57F754D1715D', '68E7C813-765F-4F9A-AF1B-F5A4CD467C8B', 3, 10500, 0, 0, 1)


данные таблицы необходимо привести к xml вида:

<UNITS>
    <RootUnit UnitID="8EA1487A-0382-4A08-87A3-E53161097D31" ObjectID="10001" UnitLevel="1">
        <Params>
             <Param ID="1" Value="100"></Param>
             <Param ID="2" Value="2000804048"></Param>
             <Param ID="3" Value="name"></Param>
        </Params>
        <ChildUnits>
             <ChildUnit ParentID="8EA1487A-0382-4A08-87A3-E53161097D31" UnitID="A22D917C-D766-4698-80E0-57F754D1715D" ObjectID="10122" UnitLevel="2">
                 <Params>
                      <Param ID="1" Value="surname"></Param>
                      <Param ID="2" Value="name"></Param>
                      <Param ID="3" Value="midname"></Param>
                 </Params>
                 <ChildUnits>
                     <ChildUnit ParentID="A22D917C-D766-4698-80E0-57F754D1715D" UnitID="B1D00795-FB48-4ED5-AE32-DEF108F878B0" ObjectID="10125" UnitLevel="3">
                         <Params>
                             <Param ID="1" Value="surname"></Param>
                             <Param ID="2" Value="name"></Param>
                             <Param ID="3" Value="midname"></Param>
                         </Params>
                         <ChildUnits/>
                         <Result>0</Result>
                     </ChildUnit>
                     <ChildUnit ParentID="A22D917C-D766-4698-80E0-57F754D1715D" UnitID="68E7C813-765F-4F9A-AF1B-F5A4CD467C8B" ObjectID="10500" UnitLevel="3">
                         <Params>
                             <Param ID="1" Value="2005"></Param>
                             <Param ID="2" Value="2000"></Param>
                         </Params>
                         <ChildUnits/>
                         <Result>0</Result>
                     </ChildUnit>
                 </ChildUnits>
                 <Result>0</Result>
             </ChildUnit>
        </ChildUnits>
        <Result>0</Result>
    </RootUnit>
</UNITS>


узлы Unit находятся по записи Type = 1, гарантированно что для каждого UnitID такая запись одна
Узел RootUnit находиться по rid = null, гарантированно что запись с rid = null также одна

вся проблема именно с выстраиванием дерева потомков и именно для xml

не вопрос получить корневой узел и список его потомков церез cte
with cte (root, id) as (
     select rid, id from @tbl_units where rid is null and type=1
        union all
     select t.rid, t.id
     from @tbl_units r
           join cte on cte.id = r.rid
     where r.type = 1
)
select * from cte


далее я подумал неплохо бы написать функцию передавая ей unitid она бы выстраивала конструкцию
<ChildUnits>
    <ChildUnit ParentID=@UnitID UnitID="somechildid" ObjectID="someunitobjectid" UnitLevel="">
        <Params>
          ...
        </Params>
        <ChildUnits/> --но вот здесь случается затык
       <Result></Result>
    </ChildUnit>
    ....
</ChildUnits>


когда мы доходим до элемента дочерних объектов нам необходимо восстановить их в виде хмл рекурсивно для текущего объекта, а на сколько мне помниться скалярки рекурсивно сами на себя не работают

поигрался с outer apply но чет мозги на сегодня не варят
есть вариант воссоздать дерево юнита в виде реляционной таблицы (unitID uniqueidentifier, xmldata xml) не содержащей изначально секции ChildUnits и потом обратным циклом восстановиться до RootUnit
но это попахивает курсором и xml.modify а хотелось бы поэлегантней

Есть предложения как лучше организовать реализацию?
8 сен 15, 21:04    [18125246]     Ответить | Цитировать Сообщить модератору
 Re: сборка xml  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
+ В качестве идеи
declare @t table (id int primary key, parent_id int null);

insert into @t
values
 (1, null), (100, null),
 (2, 1), (3, 1),
 (4, 2), (5, 2), (6, 3), (7, 3),
 (8, 4), (9, 4), (10, 4),
 (11, 5), (12, 5), (13, 5),
 (14, 6), (15, 6), (17, 6),
 (18, 7), (19, 7), (20, 7),
 (200, 100);

declare @p table (parent_id int, level int, x xml);
declare @level int = 1;

insert into @p
select
 p.parent_id, @level, a.x
from
 (select distinct parent_id from @t t where not exists(select 1 from @t where parent_id = t.id)) p cross apply
 (select id as [@id] from @t where parent_id = p.parent_id for xml path('child'), type) a(x);

while 1 = 1
begin
 insert into @p
 select
  p.parent_id, @level + 1, a.x
 from
  (select distinct t.parent_id from @p p join @t t on t.id = p.parent_id where p.level = @level and t.parent_id is not null) p cross apply
  (select t.id as [@id], (select x as [*] from @p where parent_id = t.id for xml path(''), type) as [childs] from @t t where t.parent_id = p.parent_id for xml path('child'), type) a(x);

 if @@rowcount = 0
  break;

 select
  @level += 1;
end;

select
 p.parent_id as [@id], p.x as [childs]
from
 (select id from @t where parent_id is null) t join
 @p p on p.parent_id = t.id
for xml path('child'), root('root'), type;
9 сен 15, 01:39    [18125986]     Ответить | Цитировать Сообщить модератору
 Re: сборка xml  [new]
felix_ff
Member

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

благодарю за подсказку
сейчас работаю над похожей реализацией но с несколько другим подходом, как будет готово выложу здесь :)
9 сен 15, 13:53    [18128145]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить