FOR XML EXPLICIT

Question

假设我有这样的设置：

-- 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“，那么我会得到：

<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，所以我想回到：

<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 expl显式“，但是到目前为止，在我尝试过的所有尝试中，我还没有能够获得我需要的格式。

有没有人能展示一个以所需格式返回数据的示例查询？

谢谢，马克

Answer 1

您还可以重写查询来控制xml输出，Google nested FOR XML QUERY。这是一个使用FOR XML AUTO的例子，你可以通过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>

Answer 2

诚然，这并没有回答您的问题，但总的来说，我发现带有XML显式的UNION方法是一种很好的方法，因为SQL读起来要清晰得多。下面是一个示例：

http://jdixon.dotnetdevelopersjournal.com/sql_2000s_for_xml_explicit_vs_sql_2005s_for_xml_path.htm