Дубликаты узлов для xml

Question

Следующий sql

declare @head1  table
(head_id uniqueidentifier)

declare @sub1 table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))

declare @sub2 table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))

declare @unrelated table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))

declare @gd uniqueidentifier;
set @gd = newid();

insert into @head1 (head_id) values (@gd)
insert into @sub1 (head_id,own_id,own_field) values (@gd,10,'Sub1 field')
insert into @sub2 (head_id,own_id,own_field) values (@gd,11,'Sub2 field')
insert into @unrelated (head_id,own_id,own_field) values (@gd,100,'another field')


select alldata.* from
    (select head.head_id
        ,(
            select * from 
                (
                    select sub1.own_id,sub1.own_field 
                        from @sub1 sub1 
                        where sub1.head_id = head.head_id
                        union all 
                    select sub2.own_id,sub2.own_field 
                        from @sub2 sub2 
                        where sub2.head_id = head.head_id
                ) tmp for xml path('sub'), type ) sub

        ,(select unrelated.own_id,unrelated.own_field 
            from @unrelated unrelated where unrelated.head_id = head.head_id 
            for xml path('unrelated'),type 
            ) unrelated
        from @head1 head 
        ) alldata for xml auto,root('master')

возвращает это:

<master>
  <alldata head_id="C6DF494E-4DA7-4726-8288-BB463CB96834">
    <sub>
      <sub>
        <own_id>10</own_id>
        <own_field>Sub1 field</own_field>
      </sub>
      <sub>
        <own_id>11</own_id>
        <own_field>Sub2 field</own_field>
      </sub>
    </sub>
    <unrelated>
      <unrelated>
        <own_id>100</own_id>
        <own_field>another field</own_field>
      </unrelated>
    </unrelated>
  </alldata>
</master>

Как я могу избежать узла sub-in-sub и неродственного в неродственном узле? Запрос составлен, но представляет реальную проблему. Я сделал это только для того, чтобы каждый мог повторить полученный результат. Поэтому я хотел бы, чтобы результат имел "подчиненный" и "несвязанный" узел на том же уровне, что и узел "alldata".

большое спасибо

Answer 1

Для тех, кто заинтересован, решение состояло в том, чтобы поместить запрос объединения в CTE.

declare @head1  table
(head_id uniqueidentifier)

declare @sub1 table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))

declare @sub2 table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))

declare @unrelated table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))

declare @gd uniqueidentifier;
set @gd = newid();

insert into @head1 (head_id) values (@gd)
insert into @sub1 (head_id,own_id,own_field) values (@gd,10,'Sub1 field')
insert into @sub2 (head_id,own_id,own_field) values (@gd,11,'Sub2 field')
insert into @unrelated (head_id,own_id,own_field) values (@gd,100,'another field');


with subs (head_id,own_id,own_field)
as 
( select sub1.head_id,sub1.own_id,sub1.own_field 
    from @sub1 sub1 
    union all 
    select sub2.head_id,sub2.own_id,sub2.own_field 
    from @sub2 sub2 
)

select head.head_id
  ,( select * from subs where subs.head_id = head.head_id for xml path('sub'),type )


  ,(select unrelated.own_id,unrelated.own_field 
    from @unrelated unrelated where unrelated.head_id = head.head_id 
  for xml path('unrelated'),type )
  from @head1 head 
  for xml auto,root('master')

Слава https://www.sqlservercentral.com/Forums/Users/Eirikur-Eiriksson