使用XML在SQL Server上创建计算列

 

在SQL Server数据库中，当你想使用一个数据，而这个数据不保存在表中，计算列很有用。例如，你有一张表，它包括列dollar amounts, wholesale prices和retail prices。你肯定不想在每次查询表时来计算那两列之间的差值，你希望将其值保存在第三列中，让其自动计算前两列之间的差值。而此列就是计算列。

　　在SQL Server中使用XML数据来创建计算列，你的列定义必须包含必要的用来检测向列中插入的是什么数据的表达式。例如，在上面的例子中，你的表达式应该从retail列中的值减去wholesale列中的值。当你添加或更新表中的数据行时，差值将自动插入至计算列中。

　　你可以很容易地在两个或更多的包含字符串或数字类型值的列的基础上创建计算列。(更多关于如何创建此类型的计算列的详细信息，请参考Microsoft SQL Server Books Online)。然而，如果你想要基于指定的XML列中元素值创建一个计算列，该过程相对更加复杂一些。因为你必须使用Xquery表达式来从XML列中获取指定元素数据,且SQL Server不支持在计算列的定义中使用Xquery表达式。

　　要解决此问题，可以创建一个函数来接收你想包含在计算列中的XML数据，并在计算列定义中调用此函数。更好的示范这是如何工作的，我们在这给出一个例子。我在SQL Server 2005的示例数据库AdventureWorks中创建以下的架构和表:

　　USE AdventureWorks;
　　GO
　　CREATE SCHEMA hr
　　GO
　　SELECT TOP 10 JobCandidateID AS CandidateID,
　　[Resume] AS JobResume
　　INTO hr.CandidateNames
　　FROM HumanResources.JobCandidate
　　GO

　　正如名称所示，HumanResources.JobCandidate表中的Resume列是一个XML列，它包含侯选人的履历信息。我从这张表中提取数据来创建hr架构中的CandidateNames表。(我创建了一个单独的表，因为我希望可以修改表定义，从而可以增加计算列)

　　在建立好测试环境后，你可以创建函数。函数应该包括在从指定的XML列中获取数据时所需的XQuery表达式。例如，以下函数接收工作候选人的姓名，并保存在JobResume列中：

　　CREATE FUNCTION hr.FullName (@name XML)
　　RETURNS NVARCHAR(60) AS
　　BEGIN
　　RETURN @name.value('declare namespace ns=
　　"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
　　concat((/ns:Resume/ns:Name/ns:Name.First)[1], " ",
　　(/ns:Resume/ns:Name/ns:Name.Last)[1])','nvarchar(60)')
　　END

正如你所看到的，函数FullName带一个输入参数，该参数被定义成XML类型。这个做法是当调用此函数时，可以把包含所需提取的数据的XML列名称作为输入值来使用。

Value()方法带两个参数。第一个参数定义了目标XML列使用的名称空间，第二个参数包含接收实际数据的Xquery表达式。在这个例子中，表达式使用concat()方法来连接姓与名，就像它们在XML文件中。要想了解更多的关于如何使用value()方法，以及如何创建Xquery表达式，请查看我的文章《Retrieve XML data values with XQuery》。

　　一旦创建了函数，你可以通过从hr.CandidateNames表的JobResume列中接收数据测试:

　　SELECT CandidateID, hr.FullName(JobResume) AS FullName

　　FROM hr.CandidateNames

　　正如你所看到的，我已经传入了XML列名称，将其做为函数FullName的一个参数。SELECT语句应该返回以下结果：

CandidateID

FullName

1

Shai Bassli

2

Max Benson

3

KrishnaSunkammurali

4

Stephen Jiang

5

Thierry D'Hers

6

Christian Kleinerman

7

Lionel Penuchot

8

Peng Wu

9

Shengda Yang

10

Tai Yee

　　(影响10行)

　　注意，以上结果包含姓与名，正如它们在XML列中显示的一样。如果回到函数定义，可发现在value()方法中使用的Xquery表达式指定了此表达式返回值为NVARCHAR(60)类型，以适应Unicode字符，如查询结果集的最后三行中的那些字符。

　　一旦函数经过测试，你就可以开始创建计算列：以下ALTER TABLE语句添加了FullName列到CandidateNames表中来:

　　ALTER TABLE hr.CandidateNames

　　ADDFullNameAShr.FullName(JobResume)

　　我已经在计算列表达式中使用FullName函数，并将列JobResume作为参数传入函数。在运行ALTER TABLE语句后，你可以用以下SELECT语句测试数据是否已经被插入到计算列中：

　　SELECT CandidateID, FullName FROM hr.CandidateNames

　　运行以上语句后，应该返回与上文中相同的结果集。

　　这就是在SQL Server中基于XML data数据创建的一个计算列。关键是创建一个函数来运行Xquery表达式，且稍后在计算列中使用此函数定义。要了解更多关于计算列、XML列、Xquery表达式的详细信息，请查看Microsoft SQL Server在线书籍。

 

 感谢:jude_liu

免费源自@百度空间