[转]SQL Server XML 代码编写示例

SQL Server XML 代码编写示例

 //  http://www.oracle.com/technology/global/cn/pub/articles/quinlan-xml.html

 

XML 数据类型有很多内置的方法，这些方法只针对XML数据类型。比较常用的有.query、.value、.modify、.nodes、.exist 等等方法。本文中所有的SQL 脚本范例都基于AdventureWorks 数据库进行过测试。

 

(1) .query 方法

允许用户通过运行基于XQuery 格式化的查询来访问XML。XQuery 允许返回多个数据片段，而不是一个离散的值。.Query要求一个参数，运行在XML数据实例之上有效的XQuery。

 

如下是提取Production.ProductModel 表中ProductModelID 为 66 的产品文档：

Select ProductModelID,

    Instructions.query('declare namespace PI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";

    /PI:root/PI:Location/PI:step' ) AS Steps

From Production.ProductModel

where ProductModelID=66

 

Select ProductModelID,

    Instructions.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";

    /root/Location/step' ) AS Steps

From Production.ProductModel

where ProductModelID=66

 

上述2个SQL 脚本在定义XML namespace有一些细微的差异，可以比较一下执行产生的结果。也可以在 XQuery 之外使用 With XMLNamespaces ('http://xxx' as XX)，提高XML 查询的可读性。上述代码通过WITH XMLNamespaces() 声明编写功能相同的代码：

; WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' AS PI)

 

Select ProductModelID,

    Instructions.query('/PI:root/PI:Location/PI:step') AS Steps

From Production.ProductModel

where ProductModelID=66

 

设置默认命名空间：

; WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions')

 

Select ProductModelID,

    Instructions.query('/root/Location/step') AS Steps

From Production.ProductModel

where ProductModelID=66

 

 

(2) .value 方法

是查询离散数据的方法，使用一个XPath 的语法来定位指定的结点，并得到标量值。需要确保指定的XPath 真正返回一个离散值。

查询ProductModelID为 66 的第一个Location元素中LabelHours 属性的值。

; WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions')

 

Select ProductModelID,

    Instructions.value('(/root/Location/@LaborHours)[1]', 'decimal (5,2)') AS Steps

From Production.ProductModel

where ProductModelID=66

 

如果不是属性，而是子元素，则应该去掉@符号。

 

(3) .modify 方法

是对XQuery 的扩展，以提供XQuery 的数据修改能力。这种对XQuery的扩展称作XML数据处理语言（XML Data Manipulation Language），或者XML DML。XML DML向XQuery 中添加了3条新命令：insert / delete / replace value of。其中replace value of 替代了SQL的UPDATE语句。

例如，将上述查询的LabelHours属性值，由原来的1.5小时更新为1.75小时。

; WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions')

 

UPDATE Production.ProductModel

SET Instructions.modify('replace value of (/root/Location/@LaborHours)[1] with 1.75')

WHERE ProductModelID=66

 

在UPDATE语句中，使用 replace value of 关键字来指定更新中的XML部分。

 

(4) .nodes 方法

用来获取XML块，并将之拆分多个数据行，以关系的形式存储。通过.nodes 所做的工作，本质上是将一个XML 数据的实例拆分到自己的表中。这意味着，我们需要将.nodes 的结果作为一个表，而不是一个列来看待。在.nodes 和一个数据库表之间的主要差别是，必须将.nodes 的结果交叉应用（cross apply）回指定的XML数据的来源表中。可以将cross apply 想象成某种连接，只不过使用的是cross apply关键字，而不是JOIN关键字，并且.nodes 替代了ON子句。

; WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions')

 

SELECT pm.ProductModelID,

    pmi.Location.value('./@LocationID', 'int') AS LocationID,

    pmi.Location.value('./@LaborHours', 'decimal(5,2)') AS LaborHours

FROM Production.ProductModel pm

CROSS APPLY pm.Instructions.nodes('/root/Location') AS pmi(Location)

 

将LocationID和LaborHours 信息拆分到列上，就好像它们位于关系表中一样。通过使用.nodes 方法，本质上将一个表 ProductModel 转换成了2个表（源表，以及ProductModel表中的Instructions 列所生成的.nodes 结果）。

 

下面的范例脚本将Instructions XML数据中material 子节点的数据库取出，转为以数据表n的字段 c 来显示，其中字段c 的数据类型也是XML，进一步取出c的值，并转换为nvarchar数据类型。

 

DECLARE @xml XML

SELECT @xml=Instructions FROM Production.ProductModel

WHERE ProductModelID=66

 

; WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' AS ns)

 

SELECT c.value('.','nvarchar(max)') AS NewCol

FROM @xml.nodes('/ns:root/ns:Location/ns:step/ns:material') as n(c)

 

 

 

(5) .exist 方法

.exist 方法有点像SQL中的EXISTS 语句，接受一个XQuery 表达式，并返回一个布尔值。

查询Instructions XML数据中存在LaborHours属性的数据记录。

; WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions')

 

SELECT ProductModelID, Instructions.value('(/root/Location/@LaborHours)[1]','decimal(5,2)')

FROM Production.ProductModel

WHERE Instructions.exist('/root/Location/@LaborHours')=1

 

总结一下SQL Server 2005/2008 为XML数据类型提供的函数：

 

转载于:https://www.cnblogs.com/ruyi/archive/2009/07/18/1526199.html