[翻译]SQL Server And XML: FOR XML EXPLICIT - Part 2

Part 1

我们继续。在每个agent下边，我们需要一个叫AddressCollection的节点。让我们给原来的东东加些代码：

SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID',
    NULL AS 'Agent!2!Fname!Element',
    NULL AS 'Agent!2!SSN!Element',
    NULL AS 'AddressCollection!3!Element'
UNION ALL
SELECT
    2 AS Tag, 1 AS Parent,
    NULL, AgentID, Fname, SSN, 
    NULL
FROM @agent
UNION ALL
SELECT
    3 AS Tag, 2 AS Parent,
    NULL, NULL, NULL, NULL, 
    NULL
FROM @agent
FOR XML EXPLICIT

 

我们加入了一个新的下级元素：AddressCollection。我使用了来自@agent临时表的数据，因为我们需要给每个agent记录一个AddressCollection的元素。输出如下：

<Agents>
  <Agent AgentID="1">
    <Fname>Vimal</Fname>
    <SSN>123-23-4521</SSN>
  </Agent>
  <Agent AgentID="2">
    <Fname>Jacob</Fname>
    <SSN>321-52-4562</SSN>
  </Agent>
  <Agent AgentID="3">
    <Fname>Tom</Fname>
    <SSN>252-52-4563</SSN>
    <AddressCollection />
    <AddressCollection />
    <AddressCollection />
  </Agent>
</Agents>

 

等一下！我们出现了一个问题。注意在最后一个节点处，三个AddressCollection元素被建立了。为什么会这样？为了搞明白原因，让我们看一下在没有FOR XML EXPLICIT的时候的查询结果。让我们运行一下除过FOR XML EXPLICIT之外查询语句。

Tag	Parent	Agents!1!	Agent!2!AgentID	Agent!2!Fname	Agent!2!ssn	AddressCollection!3
1	NULL	NULL	NULL	NULL	NULL	NULL
2	1	NULL	1	Vimal	123-23-4521	NULL
2	1	NULL	2	Jacob	321-52-4562	NULL
2	1	NULL	3	Tom	252-52-4563	NULL
3	2	NULL	NULL	NULL	NULL	NULL
3	2	NULL	NULL	NULL	NULL	NULL
3	2	NULL	NULL	NULL	NULL	NULL

注意tag为3的黄色的记录，注意他们都出现在查询结果的最底下。这就是为什么它们出现在XML结果最下边的原因。因此，要解决这一问题，我们需要修改一下行之间的排序。要得到正确的XML我们需要查询结果的排序如下：

Tag	Parent	Agents!1!	Agent!2!AgentID	Agent!2!Fname	Agent!2!ssn	AddressCollection!3
1	NULL	NULL	NULL	NULL	NULL	NULL
2	1	NULL	1	Vimal	123-23-4521	NULL
3	2	NULL	NULL	NULL	NULL	NULL
2	1	NULL	2	Jacob	321-52-4562	NULL
3	2	NULL	NULL	NULL	NULL	NULL
2	1	NULL	3	Tom	252-52-4563	NULL
3	2	NULL	NULL	NULL	NULL	NULL

在这一阶段，我们需要写一些代码来改变这些记录的排序。可能有很多不同的方法来做到这一点。我加入了一个基于AgentID的算术列来进行排序。下边是新的代码：

SELECT
    1 AS Tag,
    NULL AS Parent,
    0 AS Sort,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID',
    NULL AS 'Agent!2!Fname!Element',
    NULL AS 'Agent!2!SSN!Element',
    NULL AS 'AddressCollection!3!Element'
UNION ALL
SELECT
    2 AS Tag, 1 AS Parent, 
    AgentID * 100 AS Sort,
    NULL, AgentID, Fname, SSN, 
    NULL
FROM @agent
UNION ALL
SELECT
    3 AS Tag, 2 AS Parent,
    AgentID * 100 + 1 AS Sort,
    NULL, NULL, NULL, NULL, 
    NULL
FROM @agent
ORDER BY Sort

 

Tag	Parent	Sort	Agents!1!	Agent!2!AgentID	Agent!2!Fname	Agent!2!ssn	AddressCollection!3
1	NULL	0	NULL	NULL	NULL	NULL	NULL
2	1	100	NULL	1	Vimal	123-23-4521	NULL
3	2	101	NULL	NULL	NULL	NULL	NULL
2	1	200	NULL	2	Jacob	321-52-4562	NULL
3	2	201	NULL	NULL	NULL	NULL	NULL
2	1	300	NULL	3	Tom	252-52-4563	NULL
3	2	301	NULL	NULL	NULL	NULL	NULL

好的，可以工作了。注意，排序字段拥有的值使我们得到了理想的记录。可能会有很多不同的方法来产生排序列。未了达到这个例子的目的，我用AgentID乘以100、101等等。这种做法不见得在所有情况下都能正常工作，但在这个例子里可以正常的工作。这里的关键是对要排序的记录进行正确的排序，你可以应用你自己的逻辑来达到这一点。现在，让我们来生成XML吧~

SELECT
    1 AS Tag,
    NULL AS Parent,
    0 AS Sort,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID',
    NULL AS 'Agent!2!Fname!Element',
    NULL AS 'Agent!2!SSN!Element',
    NULL AS 'AddressCollection!3!Element'
UNION ALL
SELECT
    2 AS Tag, 1 AS Parent, 
    AgentID * 100 AS Sort,
    NULL, AgentID, Fname, SSN, 
    NULL
FROM @agent
UNION ALL
SELECT
    3 AS Tag, 2 AS Parent,
    AgentID * 100 + 1 AS Sort,
    NULL, NULL, NULL, NULL, 
    NULL
FROM @agent
ORDER BY Sort
FOR XML EXPLICIT

 

不幸的是，这段代码不能运行。如果你尝试运行，你将会得到一个错误：

FOR XML EXPLICIT query contains the invalid column name 'Sort'. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.

这个错误是因为我们刚刚加入的"Sort"列造成的。当我们使用FOR XML EXPLICIT时，除了"Tag"和"Parent"以外的所有列都必须以"[TAG]![TAGID]!ATTRIBUTE..."的形式出现。我们需要把"Sort"列隐藏起来。让我们添加一些代码如下：

SELECT 
    Tag, 
    Parent,
    [Agents!1!],
    [Agent!2!AgentID],
    [Agent!2!Fname!Element],
    [Agent!2!SSN!Element],
    [AddressCollection!3!Element]
FROM (
    SELECT
        1 AS Tag,
        NULL AS Parent,
        0 AS Sort,
        NULL AS 'Agents!1!',
        NULL AS 'Agent!2!AgentID',
        NULL AS 'Agent!2!Fname!Element',
        NULL AS 'Agent!2!SSN!Element',
        NULL AS 'AddressCollection!3!Element'
    UNION ALL
    SELECT
        2 AS Tag, 1 AS Parent, 
        AgentID * 100 AS Sort,
        NULL, AgentID, Fname, SSN, 
        NULL
    FROM @agent
    UNION ALL
    SELECT
        3 AS Tag, 2 AS Parent,
        AgentID * 100 + 1 AS Sort,
        NULL, NULL, NULL, NULL, 
        NULL
    FROM @agent
) A
ORDER BY Sort
FOR XML EXPLICIT

 

运行结果如下：

<Agents>
  <Agent AgentID="1">
    <Fname>Vimal</Fname>
    <SSN>123-23-4521</SSN>
    <AddressCollection />
  </Agent>
  <Agent AgentID="2">
    <Fname>Jacob</Fname>
    <SSN>321-52-4562</SSN>
    <AddressCollection />
  </Agent>
  <Agent AgentID="3">
    <Fname>Tom</Fname>
    <SSN>252-52-4563</SSN>
    <AddressCollection />
  </Agent>
</Agents>

 

Part 3