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

作者：Jacob Sebastian

=================================

如果你使用的是SQL server 2000，你可以使用EXPLICIT控制建立你的XML结构。

近来我在一些互联网论坛里帮助一些人解决在写TSQL时关于EXPLICIT的疑问。我观察到大多数程序员会因为FOR XML EXPLICIT的排序设置得到的结果问题而犯错。我和Vimal Rughani最近致力于解决这个问题。当我们搞定后，Vimal Rughani问我是不是可以把编写的过程写下来，我想把我解决问题时的步骤写下来这样能够启发其它人，这的确是个好主意。这样，可以帮助到更多的人。

想通过FOR XML EXPLICIT产生如下的xml效果：

 

<Agents>
  <Agent AgentID="1">
    <Fname>Vimal</Fname>
    <SSN>123-23-4521</SSN>
    <AddressCollection>
      <Address>
        <AddressType>Home</AddressType>
        <Address1>abc</Address1>
        <Address2>xyz road</Address2>
        <City>RJ</City>
      </Address>
      <Address>
        <AddressType>Office</AddressType>
        <Address1>temp</Address1>
        <Address2>ppp road</Address2>
        <City>RJ</City>
      </Address>
    </AddressCollection>
  </Agent>
  <Agent AgentID="2">
    <Fname>Jacob</Fname>
    <SSN>321-52-4562</SSN>
    <AddressCollection>
      <Address>
        <AddressType>Home</AddressType>
        <Address1>xxx</Address1>
        <Address2>aaa road</Address2>
        <City>NY</City>
      </Address>
      <Address>
        <AddressType>Office</AddressType>
        <Address1>ccc</Address1>
        <Address2>oli Com</Address2>
        <City>CL</City>
      </Address>
      <Address>
        <AddressType>Temp</AddressType>
        <Address1>eee</Address1>
        <Address2>olkiu road</Address2>
        <City>CL</City>
      </Address>
    </AddressCollection>
  </Agent>
  <Agent AgentID="3">
    <Fname>Tom</Fname>
    <SSN>252-52-4563</SSN>
    <AddressCollection>
      <Address>
        <AddressType>Home</AddressType>
        <Address1>ttt</Address1>
        <Address2>loik road</Address2>
        <City>NY</City>
      </Address>
    </AddressCollection>
  </Agent>
</Agents>

 

这些数据来之两个表：Agents和Addresses。在我们写查询语句之前，我们需要建立这些表并添加一些数据。对于这个例子，我们不需要建立真实的数据库表。我们可以建立临时表。下边的代码将会建立这两个临时表并加入一些数据。代码来自MSDN一个论坛的Kent。

 

/*
Borrowed from Kent's code
*/
declare @agent table
(
AgentID int,
Fname varchar(5),
SSN varchar(11)
)

insert into @agent
select 1, 'Vimal', '123-23-4521' union all
select 2, 'Jacob', '321-52-4562' union all
select 3, 'Tom', '252-52-4563'

declare @address table
(
AddressID int,
AddressType varchar(12),
Address1 varchar(20),
Address2 varchar(20),
City varchar(25),
AgentID int
)

insert into @address
select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all
select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all
select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all
select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all
select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all
select 6, 'Home', 'ttt', 'loik road', 'NY', 3

 

让我们开始写查询。因为我们写这个查询是为了学习，所以我打算采用一个循序渐进的办法来逐步完善成一个完整的查询。

现在，让我们从根节点开始。我们来写一些语句来建立这个根节点。

 

SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'Agents!1!'
FOR XML EXPLICIT

 

这将产生一个我们需要的根节点。
<Agents />

 现在，让我们写下一级的代码。下一级是节点agent，数据可以来自agent表，让我们加一些代码如下：

 

SELECT

    1 AS Tag,
    NULL AS Parent,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID'
    UNION ALL
    SELECT
    2 AS Tag,
    1 AS Parent,
    NULL, 
    AgentID

FROM @agent
FOR XML EXPLICIT

 

 

注意黄色的代码，这个是我们在以前的版本加入的东西，这个查询将产生如下输出： 

<Agents>
  <Agent AgentID="1" />
  <Agent AgentID="2" />
  <Agent AgentID="3" />
</Agents>

 

到现在看上去都还不错。现在让我们在agent下边加入fname和ssn这两个子元素。

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'
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL, 
    AgentID,
    Fname,
    SSN
FROM @agent
FOR XML EXPLICIT

 

这个版本将会给我们如下的输出：

<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>
  </Agent>
</Agents>

 

Part 2