用SQL 2000创建用户化XML流

你可能想在程序当中使用xml，但是仅仅用一个简单的查询是不能从microsoft sql server 2000中获取其信息的。
 
让我们假设你想将一个html 表格连接到一个xml数据岛，但是你十分确定要将若干表格套入母表中十分复杂。母表中的每一个表格一定是xml数据结构中父节点下的一组节点。电话号码目录就是一个很好的例子。

<table datasrc="#xmlphonedata">
<tr>
    <td>
        <span datafld="fname"></span>&nbsp;
        <span datafld="lname"></span>
    </td>
</tr>
<tr>
    <td>
        <table datasrc="#xmlphonedata" datafld="phone">
            <tr>
                <td><span datafld="phone_type"></span>:</td>
                <td><span datafld="phone_no"></span></td>
            </tr>
        </table>
    </td>
</tr>
<tr>
    <td>
        <table datasrc="#xmlphonedata" datafld="other_info">
            <tr>
                <td><span datafld="info_type"></span>:</td>
                <td><span datafld="info_data">/span></td>
            </tr>
        </table>
    </td>
</tr>
</table>

table代表一系列信息，包括目录中某个人的名字、所有的电话号码和那个人的其他信息（譬如说，地址等）。

这个xml的模型如下：

<root>
      <data>
          <lname/>
          <fname/>
          <phone>
              <phone_type/>
              <phone_no/>
          </phone>
          <other_info>
              <info_type/>
              <info_data/>
          </other_info>
      </data>
  </root>

尽管创建这个xml模型不是不可能的，我们仍然在本文中使用这个简单的例子。

首先，我们将提供一些背景知识使其更全面。数据来源于三个方面：包含员工的名（fname）和姓（lname）的职工表；包含员工电话类型（phone_type）和电话号码（phone_no）的电话表；包含员工其他信息（如地址等）的其他信息表（other_info）。

如果你想将信息从这些表中抓取到一个记录集中，sql查询可以如下：

select employee.lname, employee.fname, phone.phone_type, phone.phone_no,
other_info.info_type, other_info.info_data from employee left join phone on
 phone.employee_id = employee.employee_id left join other_info on
 other_info.employee_id = employee.employee_id

当你需要sql 2000中有xml的时候，你通常可以在查询字符串的末尾输入一个for xml auto, elements语句，你就可以得到想要的xml字符串。但是，多个节点会有一点小问题。单个节点很容易，但当你引用多个表格时，你可能不会花整天时间去尝试获得想要的xml输出。

另一个方法就是关闭行计算和数据输出。你可以通过将一个指针移入母表数据，用一个指令来操作记录，然后从带有for xml auto、elements语句的其他表格选择相关记录以分流xml输出来实现。

这个功能最好保存到一个存储程序，因为指针比较慢，而一个存储程序被事先编辑好。下面就是完成这项任务的存储程序（transact sql）。

declare @employee_id int
declare @fname varchar(50)
declare @lname varchar(50)
declare phone_cursor cursor for select employee_id, fname, lname from employees
 order by lname, fname

set nocount on

open phone_cursor

fetch next from phone_cursor into @employee_id, @fname, @lname

while @@fetch_status = 0
begin

    select '<data><fname>' + @fname + '</fname><lname>' + @lname +
 '</lname>'

    select phone_type, phone_no from phone where employee_id =
 @employee_id for xml auto, elements
    select info_type, info_data from other_info where employee_id =
 @employee_id for xml auto, elements

    select '</data>'

    fetch next from phone_cursor into @employee_id, @fname, @lname

end

close phone_cursor
deallocate phone_cursor

set nocount off
go

这几句sql创建了一个用以存储员工数据和重复执行的指针。为了解除每一个select语句后的行计算输出功能，nocount被设置为on。当指针中的每条记录在被操作的时候，构成了一个包含着xml输出的名和姓节点字符串。电话信息和其他信息也都创建了xml。

然后，父节点关闭。在成功地完成了指针操作以后，关闭指针并对其再分配。这就得到了一个符合目标xml模型的xml字符串。

为了有效的使用这个xml输出字符串，输出必须存储在一个ado流对象中。利用流对象中的readtext方法可以访问数据：

<xml id="xmlphonedata" name="xmlphonedata">
<%
dim adoconn, adocmd
dim adostream
set adoconn = server.createobject("adodb.connection")
set adocmd = server.createobject("adodb.command")
adoconn.connectionstring = "some connection string to ms sql 2k"
adoconn.open
set adocmd.activeconnection = adoconn
set adostream = server.createobject("adodb.stream")
adocmd.commandtype = 4 'adcmdstoredproc
adocmd.commandtext = "get_test_phone"
adostream.open
adocmd.properties("output stream") = adostream
adocmd.execute ,,1024 'adexecutestream

response.write adostream.readtext(-1)

adostream.close
set adostream = nothing
adoconn.close
set adocmd = nothing
set adoconn = nothing
%>
</xml>

以上代码创建了一个ado到sql 2000的连接，执行了一个存储程序。结果被储存在ado流对象（adostream）中。其数据写入response缓冲器，流对象被关闭，然后是一些“清仓”操作。

这个范例提供了一个创建用户化xml方案的一般方法。通过将html表格连接到xml数据岛，你可以创造一些针对性的解决方法。