从SQL获取XML而不使用嵌套节点 [英] Get XML from SQL without nested nodes

问题描述

使用以下查询，我得到一个结果，其中'sub'-node包含多个'sub'-nodes'和'unlated'节点，还包含'无关'节点。

如果没有外部的子和不相关节点，我怎样才能得到结果？



查询已经组成但反映了我正在使用的现实生活查询必须产生所描述的预先存在的结果。

Hi,
Using the following query, I get a result which has 'sub'-node containing multiple 'sub'-nodes and a 'unrelated' node also containing 'unrelated'-nodes.
How can I get the result without the outer 'sub' and 'unrelated' nodes?

The query is made up but reflects a real life query I'm using which must produce a pre-existing result as described.

<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>

我尝试过：

What I have tried:

declare @head1  table<br />
(head_id uniqueidentifier)<br />
<br />
declare @sub1 table (<br />
head_id uniqueidentifier,<br />
own_id int,<br />
own_field nvarchar(20))<br />
<br />
declare @sub2 table (<br />
head_id uniqueidentifier,<br />
own_id int,<br />
own_field nvarchar(20))<br />
<br />
declare @unrelated table (<br />
head_id uniqueidentifier,<br />
own_id int,<br />
own_field nvarchar(20))<br />
<br />
declare @gd uniqueidentifier;<br />
set @gd = newid();<br />
<br />
insert into @head1 (head_id) values (@gd)<br />
insert into @sub1 (head_id,own_id,own_field) values (@gd,10,'Sub1 field')<br />
insert into @sub2 (head_id,own_id,own_field) values (@gd,11,'Sub2 field')<br />
insert into @unrelated (head_id,own_id,own_field) values (@gd,100,'another field')<br />
<br />
<br />
select alldata.* from<br />
    (select head.head_id<br />
        ,(<br />
            select * from <br />
                (<br />
                    select sub1.own_id,sub1.own_field <br />
                        from @sub1 sub1 <br />
                        where sub1.head_id = head.head_id<br />
                        union all <br />
                    select sub2.own_id,sub2.own_field <br />
                        from @sub2 sub2 <br />
                        where sub2.head_id = head.head_id<br />
                ) tmp for xml path('sub'), type ) sub<br />
<br />
        ,(select unrelated.own_id,unrelated.own_field <br />
            from @unrelated unrelated where unrelated.head_id = head.head_id <br />
            for xml path('unrelated'),type <br />
            ) unrelated<br />
        from @head1 head <br />
        ) alldata for xml auto,root('master')

推荐答案

对于那些感兴趣的人，解决方案包括将联合查询放入CTE。

For those interested, solution consisted of putting the union query into a 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

Kudos to https://www.sqlservercentral.com/Forums/Users/Eirikur-Eiriksson

这篇关于从SQL获取XML而不使用嵌套节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！