用 T-SQL 操作 XML

  • 项目

Rick Dobson

在本文中，Rick Dobson 快速而有效地说明了如何使用 XML 模板。在阅读本文时，您可能还想阅读或重读 Rick 在 2004 年 1 月发表的关于使用 Web 服务工具包来创建 Web 服务及其客户端的文章、Tom Moreau 在 2001 年 3 月撰写的关于将 XML 传送到存储过程的专栏，以及后来 Anton Britten 在 2002 年 2 月发表的关于“让 XML 重新上台”的文章。

您是否注意到，过去几年的“热门话题”中有多少需要那些与典型 SQL Server DBA 的常用技能完全不同的技能集？例如，如果我们能够利用现有技能（如编写 T-SQL）将查询结果作为在 Web 上传递的 XML 文档来返回，不是很好吗？不过，Microsoft 在设计 XML 模板技术的功能集时可能考虑到了这一点。这一技术最初随附在 SQL Server 2000 中，并在后续的 Web 版本中略有调整。使用这一技术，浏览器客户端可以调用 T-SQL 语句（这些语句嵌入在一个虚拟目录中的 XML 模板中）并显示 XML 结果。这些模板可以接受参数值，您甚至可以允许客户端动态更新数据库。这些模板的 XML 语法是非常简单的，并且 T-SQL 也无疑是大家所熟悉的。

由于 XML 是以标记分隔的文本，因此客户端很容易在 Web 浏览器中读取 XML。例如，如果您的应用程序要求更为传统的格式化输出，您可以使用 XSLT（XSL 转换）将 XML 结果集转换为一个 HTML 表或某个其他布局。在本文中，我将为您展示如何将此技术与 SQLXML 3.0 SP1 for SQL Server 2000 一起使用。*【相对于可用于服务器编程的可选 FOR XML 子句，SQLXML 技术允许客户端通过编程支持 SQL Server。— Ed.】*如果您没有最新版本，可以从 www.msdn.com/sqlxml 下载。

XML 模板快速回顾

XML 模板依赖于三个 SQL Server 2000 功能。第一，模板必须驻留在一个指向 SQL Server 数据库的特殊虚拟 IIS 目录中。第二，您必须将一个 FOR XML 子句追加到返回结果的任一 T-SQL 语句中。（这个子句可让您指定结果的格式，并指定结果集的 XML 格式化发生的位置。）第三，您需要将 T-SQL 语句嵌入一个 XML 文档（XML 模板）中。

SQL Server 2000 最初随附有 IIS 虚拟目录管理客户端工具，但后续的 Web 版本修改了此工具的功能。要启动该工具，请选择 Start | SQLXML 3.0 | Configure IIS Support。我使用该工具创建了一个名为 SPTemplates 的目录，该目录带有一个名为 MyTemplates 并具有一个模板类型的嵌套文件夹，并且 SPTemplates 目录指向示例 pubs 数据库。（您可以对虚拟目录指定一个任意名称，但是如果您要使用 XML 模板，则该目录必须 包含一个模板类型文件夹。）除非您指定 Windows 集成身份验证，否则所有用户必须用同一 SQL Server 登录来登录。不过，您可以用传统的 SQL Server 安全设置来控制此登录以及 Windows 登录的权限。

将 FOR XML 子句追加到多个公共 T-SQL 查询语句的结尾。子句的三个公共参数包括 RAW、AUTO 和 NESTED。若您返回基于单个表的查询结果，RAW 就足够了；返回的 XML 文档将为列值的每一行包含一个行元素，并将其作为属性值显示。对于基于两个或更多表格的查询结果以及基于视图的查询来说，AUTO 和 NESTED 参数比较合适。正如您将在本文后面看到的，任何参数都可以显示带有其来源标记的嵌套结果。

只有当您为查询结果指示客户端格式设置时，NESTED 参数才是合法的。在从一个负载很重的 SQL Server 检索结果时，客户端格式设置能够提高可扩展性。当您用客户端格式设置指定 NESTED 参数时，您的查询语句可以包含 GROUP BY 子句和聚合函数，这些都不 能在 RAW 和 AUTO 参数中使用。

在给定虚拟目录的模板文件夹中，您可以指定多个 XML 模板查询，还可以嵌入数据库维护语句，如 INSERT 和 DELETE。每个 XML 模板查询在其 XML 文档中都必须具有唯一的根标记，并且该根标记必须包含一个对 urn:schemas-microsoft-com:xml-sql 命名空间的引用，该命名空间定义特殊的模板元素，如 query、header 和 param。您用开始和结束标记来表示一个元素；T-SQL 语句嵌入在开始和结束 query 标记之内。您可以用 query 标记的属性来指定客户端格式设置，如果您的 T-SQL 语句引用参数，则可以用嵌入在 header 标记中的 param 标记来声明它们。

返回查询结果

清单 1 显示 ListAuthors.xml 文件的内容，该文件是一个从 authors 表中按姓氏返回所有作者姓名的模板查询。由于我的 SPTemplates 虚拟目录指向 pubs 数据库，因此该查询引用该数据库中的 authors 表。在清单 1 中，根标记用 XML 模板查询的元素定义指定命名空间。（您可以随意使用根标记的任何唯一名称来取代 ROOT。）

清单 1. 对 authors 表中选定列的模板查询。

<ROOT 
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:query>
        SELECT au_fname, au_lname 
        FROM authors 
        ORDER BY au_lname 
        FOR XML AUTO
      </sql:query>
</ROOT>

图 1 显示包含 ListAuthors.xml 查询结果的浏览器窗口。地址框显示调用 SPTemplates 虚拟目录的 MyTemplates 文件夹中的模板查询的 URL，此虚拟目录位于名为 cablat 的 IIS 服务器上。由于我在清单 1 的 FOR XML 子句中使用了 AUTO 参数，所以每行都显示了作为查询源的表的名称。如果我使用 RAW 代替 AUTO，那么行将在结果集的每一行中替换作者。

在许多应用程序中，您需要让用户在运行时微调结果集的内容，参数是实现这个功能的常见方法。清单 2 中的 ListSearchedAuthors.xml 模板查询将允许用户（屏住您的呼吸！）在运行时指定作者的姓氏。如果用户没有指定姓氏，查询将返回一个姓氏为 Bennet 的默认作者。Param 标记允许您为模板查询指定参数。您需要做的所有事情就是使用 param 标记的 name 属性来指定一个参数的名称，然后在此查询的 T-SQL 中将一个 @ 符号放在这个名称的前面。在开始和结束 param 标记之间的值表示默认的参数值。

清单 2. 演示参数语法的模板查询。

<ROOT 
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:header>
        <sql:param name='searchname'>Bennet
        </sql:param>
      </sql:header>
      <sql:query>
        SELECT au_fname, au_lname
        FROM authors
        WHERE au_lname = @searchname
        ORDER BY  au_lname
        FOR XML AUTO
      </sql:query>
</ROOT>

图 2 显示调用 ListSearchedAuthors.xml 模板查询的结果。用户可以指定一个参数值，方法是：将一个问号 (?) 放在模板查询 URL 的后面，并且后面还跟有一对用等号 (=) 彼此分隔的值。该值对中的第一项是参数名称，第二项是参数值。由于有两名姓 Ringer 的作者，结果集将包含两个以 authors 标记开始的行。

下一个清单显示了一个名为 ListTitlesForAuthors.xml 的更为复杂的模板查询。这个查询中的 T-SQL 语句联接了三个表来显示某个作者的书名。通过将作者的姓氏指定为一个参数，用户可以指定他们感兴趣的作者，并且查询再一次使用 Bennet 作为默认作者。清单 3 中的 ListTitlesForAuthors.xml 显示了用于实现这些规范以及指定客户端格式设置的语法。首先，它将值 1 指定给 client-side-xml 属性。然后，它将 NESTED 指定为 FOR XML 参数。

清单 3. 具有参数和客户端格式设置的模板查询。

<ROOT 
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:header>
        <sql:param name='searchname'>
        Bennet</sql:param>
      </sql:header>
      <sql:query client-side-xml="1">
        SELECT a.au_fname, a.au_lname, t.title
        FROM authors a JOIN titleauthor ta
        ON a.au_id = ta.au_id 
        JOIN titles t
        ON ta.title_id = t.title_id
        WHERE au_lname=@searchname
        FOR XML NESTED
      </sql:query>
</ROOT>

图 3 显示了对姓氏为 Ringer 的作者调用 ListTitlesForAuthors.xml 的结果。您可以看到两个 Ringer（没有双关意）合著了一本书，但他们各自还撰写了另外一本书。Titles 表的查询结果嵌套显示在 authors 表的每一行中。

用模板查询更新数据库

除了在浏览器中将结果集作为 XML 文档返回以外，XML 模板查询还允许您用 T-SQL 关键字执行数据库维护任务。在清单 4 中，InsertTitleAuthor.xml 模板查询在这三个表（titles、authors 和 titleauthor 表）的每一个中输入一行。请注意，该清单使用 INSERT 关键字为新行指定选定的列值。此外，示例代码还演示了在单个模板查询中使用多个参数。（一个注释行标记了参数声明的开始部分。）每个参数都有一个默认值，但是用户在调用模板查询时可以重写这些值。 清单 4. 在 titles、authors 和 titleauthor 表中插入新行的模板查询。

<ROOT 
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<!-- Define parameters and defaults -->
<sql:param name=
    'in_au_id'>123-45-6789</sql:param>
<sql:param name='in_au_fname'>Rick</sql:param>
<sql:param name='in_au_lname'>Dobson</sql:param>
<sql:param name='in_title_id'>PC1234</sql:param>
<sql:param name=
    'in_title'>Database Development</sql:param>
</sql:header>
<sql:query client-side-xml="1">
   INSERT INTO titles (title_id, title)
   VALUES (@in_title_id, @in_title)
   INSERT INTO authors 
       (au_id, au_fname, au_lname, contract)
   VALUES 
       (@in_au_id, @in_au_fname, @in_au_lname, 1)
   INSERT titleauthor (au_id, title_id)
   VALUES (@in_au_id,@in_title_id)
</sql:query>
</ROOT>

在插入行时，具有多个参数是很常见的。指定多个参数的语法与指定单个参数的语法相同，除非您用 and 符号 (&) 将名-值对彼此分开。InsertTitleAuthor.xml 模板查询的默认设置指定了 Rick 和 Dobson 的作者名和姓的值。例如，用户可以通过在浏览器的地址框中输入下列内容，用自定义的 Rickie 和 Dobs 值重写这些默认设置：

http://cablat/SPTemplates/MyTemplates/
InsertTitleAuthor.xml?in_au_fname=Rickie& 
in_au_lname=Dobs

对代码而言，从表中删除行更为容易一些。您需要做的所有事情就是指定一个或多个您要对其应用 DELETE 关键字的行，您可以通过使用行的主键值作为任何 DELETE 语句中 WHERE 子句的参数来完成这项操作。清单 5 中的 DeleteTitleAuthor.xml 模板查询从 titleauthor 表中删除了一行。从 titleauthor 表中删除外键引用之后，该查询还从 authors 和 titles 表中各删除一行。

清单 5. 用于从 titleauthor、authors 和 titles 表中删除一行的模板查询。

<ROOT 
   xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<sql:param name= 
  'in_au_id'>123-45-6789</sql:param>
<sql:param name=
  'in_title_id'>PC1234</sql:param>
</sql:header>
<sql:query client-side-xml="1">
    DELETE FROM titleauthor
    WHERE au_id = 
        '123-45-6789' and title_id = 'PC1234'
    DELETE FROM authors
    WHERE au_id = '123-45-6789'
    DELETE FROM titles
    WHERE title_id = 'PC1234'
</sql:query>
</ROOT>

小结

XML 模板查询是一种在浏览器中生成 XML 输出的简单而快捷的方法。虽然您可以使用除 T-SQL 之外的其他内容来填充模板，但是 SQL Server 专家都很欣赏能够利用 T-SQL 技术生成 XML 的方法。除了为浏览器创建 XML 格式的内容以外，您还可以使用模板查询来操作 SQL Server 数据库中的值。您现在已经了解如何使用参数在运行时动态执行数据访问和数据操作任务。那么就体验一下吧！

Download 407RICK.TXT

链接到 www.msdn.com/sqlxml

提要栏：SQL Server 2005 中的 XML

请参阅 https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp 上的“XML Support in Microsoft SQL Server 2005”。

  • XML 是一个原生数据类型，它由 XML 架构（在这种情况下，该架构称为类型化 XML）有选择地进行约束。如果您事先不了解该架构或者它很复杂，那么非类型化 XML 比较适用。XML 列也可以由大多数 T-SQL 约束条件（而不是唯一键、主键或外键）来约束。

  • XML 值（最大为 2 GB/实例）在内部存储为 BLOB。

  • XML 层次结构最大限制为 128 级。

  • 您可以使用一个新的 DDL 语句在类型化和非类型化的 XML 数据列上创建索引。XML 列上的第一个索引（它需要主键列上的聚集索引）是主索引 — 列中 XML 实例的所有标记、值和路径上的 B+tree 索引。次要 XML 索引可以创建为 PATH、PROPERTY 和 VALUE 索引。

  • SET SHOWPLAN_XML ON 允许将 XML 的 showplan 作为一个单值结果集来传递。

  • XML 架构是可选的，但是一旦被定义 (CREATE XML SCHEMA COLLECTION)，XML 架构集合就存储为系统元数据的一部分，其节点为 Unicode 格式。

  • 有一个新的内置函数：XML_SCHEMA_NAMESPACE()。

  • XPathDocument 替代 XmlDocument 作为主 XML 存储。

  • 有用于 XML 数据的 CLR 类型访问器。

  • 支持 XQUERY 的子集。XQUERY — 想象一下 FLWOR (For/Let/Where/OrderBy/Return)。

  • 有五种 XML 数据类型方法：query()、value()、exist()、nodes() 和 modify()。

提要栏：其他资源

— kw

有关 SQL Server Professional 和 Pinnacle Publishing 的详细信息，请访问其网站 http://www.pinpub.com/

注：这不是 Microsoft Corporation 的网站。Microsoft 对该网站的内容不承担责任。

转到原英文页面