Go homepage(回首页)
Upload pictures (上传图片)
Write articles (发文字帖)

The author:(作者)归海一刀
published in(发表于) 2014/2/1 0:10:52
Sql，server，2005的XML最佳实施策略(3)_[SQL，Server教程]

示例：使用 CLR 流式表值函数的解决方案

该解决方案包括以下步骤：(a) 定义 CLR 类 SqlReaderBase，它实现了 ISqlReader，并且通过在 XML 实例上应用路径表达式来生成流式表值输出；(b) 创建一个程序集和一个 T-SQL 用户定义函数 (UDF) 来激活该 CLR 类；(c) 使用 UDF 定义插入、更新和删除触发器，以维护属性表。

首先，创建流式 CLR 函数，其主干如下所示。XML 数据类型被公开为 ADO.NET 中的托管类 SqlXml；它支持返回 XmlReader 的方法 CreateReader()。

public class c_streaming_xml_tvf {
public static ISqlReader streaming_xml_tvf 
(SqlXml xmlDoc, string pathExpression) {
return (new TestSqlReaderBase (xmlDoc, pathExpression));
}
}
// Class that implements ISqlReader
public class TestSqlReaderBase : ISqlReader {
XPathNodeIterator m_iterator; 
???public SqlChars FirstName;
// Metadata for current resultset
private SqlMetaData[] m_rgSqlMetaData; 
public TestSqlReaderBase (SqlXml xmlDoc, string pathExpression) { 
// Variables for XPath navigation
XPathDocument xDoc;
XPathNavigator xNav;
XPathExpression xPath;
// Set sql meta data
m_rgSqlMetaData = new SqlMetaData[1];
m_rgSqlMetaData[0] = new SqlMetaData ("FirstName", 
SqlDbType.NVarChar,50); 
//Set up the Navigator
if (!xmlDoc.IsNull)
xDoc = new XPathDocument (xmlDoc.CreateReader());
else
xDoc = new XPathDocument ();
xNav = xDoc.CreateNavigator();
xPath = xNav.Compile (pathExpression);
m_iterator = xNav.Select(xPath);
}
public bool Read() {
bool moreRows = true;
if (moreRows = m_iterator.MoveNext())
???FirstName = new SqlChars (m_iterator.Current.Value);
return moreRows;
}
}

接下来，创建一个程序集，以及一个与 CLR 函数 streaming_xml_tvf 对应的 T-SQL 用户定义函数 SQL_streaming_xml_tvf（未显示）。该 UDF 用于定义表值函数 CLR_udf_XML2Table 以便生成行集：

create function CLR_udf_XML2Table (@pk int, @xCol xml)

returns @ret_Table table (FK int, FirstName varchar(max))

with schemabinding

as

begin

insert into @ret_Table

select @pk, FirstName

FROM SQL_streaming_xml_tvf (@xCol, '/book/author/first-name')

return

end

最后，定义触发器，如示例创建触发器以填充属性表中所示，但用函数 CLR_udf_XML2Table 替换 udf_XML2Table。因此，插入触发器将如下所示：

create trigger CLR_trg_docs_INS on T for insert

as

declare @wantedXML xml

declare @FK int

select @wantedXML = xCol from inserted

select @FK = PK from inserted

insert into tblPropAuthor

select *

from dbo.CLR_udf_XML2Table(@FK, @wantedXML)

删除触发器与非 CLR 版本完全相同，而更新触发器只是将函数 udf_XML2Table() 替换为 CLR_udf_XML2Table()。

XML 架构集合

XML 架构集合是一个元数据实体，其范围由关系架构确定，包含一个或多个可能相关（例如，通过 ）或无关的 XML 架构。XML 架构集合中的单独 XML 架构由其目标命名空间标识。

XML 架构集合是使用 CREATE XML SCHEMA COLLECTION 语法创建的，并且提供了一个或多个 XML 架构。可以向现有 XML 架构中添加更多的 XML 架构组件，并且可以使用 ALTER XML SCHEMA COLLECTION 语法向 XML 架构集合中添加更多的架构。可以使用 SQL Server 2005 中的安全模型像任何 SQL 对象那样保证 XML 架构集合的安全。

多类型化列

XML 架构集合 C 按照多个 XML 架构将 XML 列 xCol 类型化。此外，标志 DOCUMENT 或 CONTENT 分别指定 XML 树或片段是否可以存储在列 xCol 中。

对于 DOCUMENT，每个 XML 实例都会按照用来对其进行验证和类型化的命名空间，指定实例中顶级元素的目标命名空间。另一方面，对于 CONTENT，每个顶级元素都可以指定 C 中的任一目标命名空间。XML 实例将按照实例中存在的所有目标命名空间进行验证和类型化。

架构演变

XML 架构集合用于类型化 XML 列、变量和参数。它提供了一种 XML 架构演变机制。假设您将带有目标命名空间 BOOK-V1 的 XML 架构添加到 XML 架构集合 C 中。使用 C 加以类型化的 XML 列 xCol 可以存储符合 BOOK-V1 架构的 XML 数据。

假设某个应用程序希望通过新的架构组件（例如复杂类型定义和顶级元素声明）来扩展 XML 架构。这些新的架构组件可以添加到 BOOK-V1 架构中，并且不要求对列 xCol 中的现有 XML 数据进行重新验证。

假设该应用程序后来希望提供该 XML 架构的新版本，并且为该新版本选择目标命名空间 BOOK-V2。该 XML 架构可以添加到 C 中。XML 列可以存储 BOOK-V1 和 BOOK-V2 二者的实例，并且对符合这些命名空间的 XML 实例执行查询和数据修改。

返回页首

用法

加载 XML 数据

将 XML 数据从 SQL Server 2000 传输到 SQL Server 2005

可以用多种方式将 XML 数据传输到 SQL Server 2005。在下一节中，我们将讨论几种方案。

• 如果您将数据存储在 SQL Server 2000 数据库的 [n]text 或图像列中，可以使用 DTS 等将表导入到 SQL Server 2005 数据库中。使用 ALTER TABLE 语句将列类型更改为 XML。

• 可以使用 bcp out 批量复制 SQL Server 2000 中的数据，使用 bcp in 将数据批量插入到 SQL Server 2005 数据库中。

• 如果您将数据存储在 SQL Server 2000 数据库的关系列中，请创建一个带有一个 ntext 列的新表，同时根据需要在该表中创建一个主键列以用作行标识符。使用客户端编程检索在服务器中通过 FOR XML 生成的 XML，并且将其写入 ntext 列。然后，使用上述技巧将数据传输到 SQL Server 2005 数据库。您可以选择将 XML 直接写入 SQL Server 2005 数据库的 XML 列中。

示例：将列类型更改为 XML

假设您需要将表 R 中的 [n]text 或图像列 XYZ 的类型更改为非类型化 XML。下面的语句可执行此类更改：

ALTER TABLE R ALTER COLUMN XYZ XML

• 如果需要，可以通过指定一个 XML 架构集合将目标类型化为 XML。

批量加载 XML 数据

可以使用 SQL Server 中的批量加载功能（如 bcp），将 XML 数据批量加载到服务器中。通过 OPENROWSET 可以将文件中的数据加载到 XML 列中。下面的示例阐明了这一点。

示例：从文件中加载 XML

该示例说明了如何在表 T 中插入行。XML 列的值作为 CLOB 从文件 C:\yukon\xmlfile.xml 中加载，并且整数列被提供了值 10。

INSERT INTO T

SELECT 10, xCol

FROM (SELECT *

FROM OPENROWSET (BULK 'C:\Yukon\xmlfile.xml', SINGLE_CLOB)

AS xCol) AS R(xCol)

文本编码

SQL Server 2005 用 Unicode (UTF-16) 存储 XML 数据。从服务器中检索的 XML 数据采用 UTF-16 编码；如果您需要不同的编码，则需要对检索到的数据执行必要的转换。有时，您可能拥有采用不同编码的 XML 数据，因此在数据加载过程中需要非常小心：

• 如果文本 XML 采用 Unicode (UCS-2, UTF-16)，则将其赋给 XML 列、变量或参数不会带来任何问题。

• 如果编码不是 Unicode 并且是隐式的（由于源代码页），则数据库中的字符串代码页应该与要加载的代码点相同或兼容（必要时使用 COLLATE）。如果不存在这样的服务器代码页，则您必须添加带有正确编码的显式 XML 声明。

• 要使用显式编码，请使用 varbinary() 类型（它不与代码页交互）或者使用适当代码页的字符串类型。然后，将该数据赋给 XML 列、变量或参数。

示例：显式指定编码

假设您具有的 XML 文档 (vcdoc) 被存储为没有显式 XML 声明的 varchar(max)。下面的语句将添加一个带有编码"iso8859-1"的 XML 声明，将 XML 文档串连起来，将结果转换为 varbinary(max) 以便保留字节表示形式，最后将其转换为 XML。这使 XML 处理器能够按照指定的编码"iso8859-1"来分析数据，并为字符串值生成相应的 UTF-16 表示形式。

SELECT CAST(

CAST ((''+ vcdoc)

AS VARBINARY (MAX))

AS XML)

Xquery 与类型推理

嵌入到 T-SQL 中的 XQuery (http://www.w3.org/TR/xquery/) 语言支持查询 XML 数据类型。该语言正在由 WWW 联合会 (W3C) 进行开发（在本文作者最后一次召集起来撰写本文时），并且所有主要数据库供应商（包括 Microsoft）都参与了开发工作。它包括了 XPath 2.0 作为导航语言。同时，还提供了针对 XML 数据类型的数据修改语言构造。有关 SQL Server 2005 中支持的 Xquery 构造、函数和运算符的信息，请参阅联机图书。

错误模型

具有语法错误的 Xquery 表达式和 XML DML 语句会返回编译错误。编译阶段会检查 XQuery 表达式和 DML 语句的静态类型正确性，并且对于类型化 XML 使用 XML 架构进行类型推理。如果某个表达式可能在运行时由于类型安全冲突而失败，它会引发静态类型错误。静态错误的例子有将字符串添加到整数以及在不存在的节点中查询类型化数据。

与 W3C 标准有所不同的是，XQuery 运行时错误被转换为可以作为空 XML 或 NULL 传播给查询结果的空序列（具体取决于调用上下文）。

通过显式转换到正确的类型，用户可以避免静态错误，尽管运行时转换错误将被转化为空序列。

下面的小节将详细讨论类型检查。

唯一性检查

如果编译器无法确定能否在运行时保证唯一性，则要求唯一性的定位步骤、函数参数和运算符（例如 eq）将返回错误。问题经常出现在非类型化数据上。例如，属性查找要求存在唯一的父元素；能够选择单个父节点的序号即可满足需要。计算 node()-value() 组合（请参阅 Value()、Nodes() 和 OpenXML()）以提取属性值，这可能不需要指定序号，如下面的示例所示。

示例：已知的唯一性

在该示例中，nodes() 方法为每个 元素生成一个单独的行。（有关 nodes() 方法的详细说明，请参阅 Value()、Nodes() 和 OpenXML()）。在 节点上进行求值的 value() 方法会提取 @genre（它作为属性具有唯一性）的值。

SELECT nref.value('@genre', 'varchar(max)') LastName

FROM T CROSS APPLY xCol.nodes('//book') AS R(nref)

XML 架构用于对类型化 XML 进行类型检查。如果节点被指定为 XML 架构中的唯一节点，则编译器将使用该信息，并且不会出现任何错误。否则，需要能够选择单个节点的序号。特别地，如果使用子代或自身轴 (//)，例如 /book//title，则会丢失

赞