对于 XML 显式 [英] FOR XML EXPLICIT

问题描述

假设我有这个设置:

-- tables
declare @main table (id int, name varchar(20))
declare @subA table (id int, mid int, name varchar(20))
declare @subA1 table (id int, subAid int, name varchar(20))
declare @subA2 table (id int, subAid int, name varchar(20))
declare @subB table (id int, mid int, name varchar(20))

-- sample data
insert @main values (1, 'A')
insert @main values (2, 'B')
insert @SubA values (1, 1, 'A')
insert @SubA values (2, 1, 'B')
insert @SubA values (3, 2, 'C')
insert @SubA1 values (1, 1, 'A')
insert @SubA2 values (1, 2, 'A')
insert @SubB values (1, 1, 'A')
insert @SubB values (2, 1, 'B')
insert @SubB values (3, 2, 'C')

-- results
select m.id, m.name, a.name, a1.name, a2.name, b.name
from @main m
left outer join @SubA a on m.id = a.mid
left outer join @SubA1 a1 on a.id = a1.subAid
left outer join @SubA2 a2 on a.id = a2.subAid
left outer join @SubB b on m.id = b.mid

返回:

1   A   A   A   NULL    A
1   A   A   A   NULL    B
1   A   B   NULL    A   A
1   A   B   NULL    A   B
2   B   C   NULL    NULL    C

如果我使用for xml auto"然后我得到:

If I use "for xml auto" then I get:

<m id="1" name="A">
  <a name="A">
    <a1 name="A">
      <a2>
        <b name="A" />
        <b name="B" />
      </a2>
    </a1>
  </a>
  <a name="B">
    <a1>
      <a2 name="A">
        <b name="A" />
        <b name="B" />
      </a2>
    </a1>
  </a>
</m>
<m id="2" name="B">
  <a name="C">
    <a1>
      <a2>
        <b name="C" />
      </a2>
    </a1>
  </a>
</m>

然而，这不是我需要的.我想展示的是@main 是主表，它有两个孩子:@subA 和@SubB.@SubA 反过来也有两个孩子:@SubA1 和@SubA2，所以我想回来:

However, this isn't what I need. What I want to show is that @main is the main table which has two children: @subA and @SubB. @SubA in turn also has two children: @SubA1 and @SubA2, so I would like to get back:

<m id="1" name="A">
  <a name="A">
    <a1 name="A"></a1>
    <a2></a2>    
  </a>
  <a name="B">
    <a1></a1>
    <a2 name="A"></a2>    
  </a>
  <b name="A" />
  <b name="B" />  
</m>
<m id="2" name="B">
  <a name="C">
    <a1></a1>
    <a2></a2>    
  </a>
  <b name="C" />  
</m>

我很确定我将不得不使用for xml explicit"，但在我迄今为止尝试过的所有尝试中，我还没有能够获得我需要的格式.

I'm pretty sure that I will have to use "for xml explicit", but out of all the attempts I have tried so far I haven't been able to get the format that I need.

谁能展示一个以所需格式返回数据的示例查询?

Can anyone show an example query that will return the data in the required format?

谢谢，标记

推荐答案

你也可以重新编写查询来控制xml输出，谷歌nested FOR XML QUERY.这是一个使用 FOR XML AUTO 的示例，您可能可以通过 FOR XML PATH 使用此技术获得更好的控制.

You can also re-write query to control the xml output, Google nested FOR XML QUERY. Here is an example using FOR XML AUTO, you could probably get better control using this technique with FOR XML PATH.

-- tables
declare @main table (id int, name varchar(20))
declare @subA table (id int, mid int, name varchar(20))
declare @subA1 table (id int, subAid int, name varchar(20))
declare @subA2 table (id int, subAid int, name varchar(20))
declare @subB table (id int, mid int, name varchar(20))

-- sample data
insert @main values (1, 'm(1)')
insert @main values (2, 'm(2)')
insert @SubA values (1, 1, 'm(1)/a(1)')
insert @SubA values (2, 1, 'm(1)/a(2)')
insert @SubA values (3, 2, 'm(2)/a(3)')
insert @SubA1 values (1, 1, 'a(1)/a1(1)')
insert @SubA2 values (1, 1, 'a(1)/a2(1)')
insert @SubA2 values (2, 2, 'a(2)/a2(2)')
insert @SubB values (1, 1, 'm(1)/b(1)')
insert @SubB values (2, 1, 'm(1)/b(2)')
insert @SubB values (3, 2, 'm(2)/b(3)')

SELECT  m.id
       ,m.name
       ,( SELECT    [name]
                   ,( SELECT    [name]
                      FROM      @subA1 AS a1
                      WHERE     a1.subAid = a.id
                    FOR XML AUTO, TYPE
                    )
                   ,( SELECT    [name]
                      FROM      @subA2 AS a2
                      WHERE     a2.subAid = a.id
                    FOR XML AUTO, TYPE
                    )
          FROM      @SubA AS a
          WHERE     m.id = a.mid
        FOR XML AUTO, TYPE
        )
       ,( SELECT    [name]
          FROM      @SubB AS b
          WHERE     m.id = b.mid
        FOR XML AUTO, TYPE
        )
FROM    @main AS m
FOR XML AUTO

返回:

<m id="1" name="m(1)">
  <a name="m(1)/a(1)">
    <a1 name="a(1)/a1(1)" />
    <a2 name="a(1)/a2(1)" />
  </a>
  <a name="m(1)/a(2)">
    <a2 name="a(2)/a2(2)" />
  </a>
  <b name="m(1)/b(1)" />
  <b name="m(1)/b(2)" />
</m>
<m id="2" name="m(2)">
  <a name="m(2)/a(3)" />
  <b name="m(2)/b(3)" />
</m>

这篇关于对于 XML 显式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！