SQL Server 2008 数据类型-xml 字段类型如何用?

作者:vkvi 来源:ITPOW(原创) 日期:2009-4-10

从 SQL Server 2005 开始,就增加了 xml 字段类型,也就是说可以直接把 xml 内容存储在该字段中,并且 SQL Server 会把它当作 xml 来对待,而不是当作 varchar 来对待。

  • SQL Server 中以 Unicode(UTF-16) 来存储 XML 数据。

  • XML 字段最多可存储 2G 的数据。

  • 可以像插入字符串一样向 XML 字段写入内容。

  • 当在 xml 数据类型实例中存储 XML 数据时,不会保留 XML 声明(如 <?xml version='1.0'?>)。

  • 插入的 xml 内容的属性的顺序可能会与原 xml 实例的顺序变化。

  • 不保留属性值前后的单引号和双引号。

  • 不保留命名空间前缀。

  • 可以对 XML 字段中的 XML 内容建立索引。方法:选中字段,点击工具栏“管理 XML 索引”图标。

  • 可以对 XML 字段中的 XML 内容建立约束,比如 age 节点必须大于等于 18。

  • 可以通过创建架构来对 XML 进行类型化,比如让 xml 内容的 <user> 节点下面必须有 <fullname> 节点。

xml 数据类型方法

下面谈谈如何查询 xml 数据,注意大小写,另外下面的示例是建立在 T-SQL 基础上的,@xml 变量相当于表中的一个 xml 字段。更多内容请参见 http://msdn.microsoft.com/zh-cn/library/ms190798.aspx

query

SELECT @xml.query('/Root/ProductDescription/Features')

返回 Features 节点及其子节点。

value

这个 value 很重要,是精髓。

SELECT @xml.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )

@xmlxml 类型的变量,也可以是换成 xml 类型的字段,比如:xmlField,不过字段通常可以用 [] 引起来。

返回 Root 节点下面的 ProductDescription 节点中的 ProductID 的属性值。即使只有一个 ProductID,那么也需要显式地指明 [1],表示第一个符合条件的节点。'int' 表示将该属性值转换成 int 类型返回。

  • 取属性值用 @,取节点值用 text()。

  • 如果属性或节点不存在,会出错吗?不会。

  • 如果属性或节点不存在,转换成 int 会出错吗?不会。

  • 如果值不是 int,要求转换成 int,会出错吗?会。

  • 如果放在 where 中,可以用操作符判断。

exist

SELECT @xml.exist('/Somedate[(text()[1] cast as xs:date ?) = xs:date("2002-01-01Z") ]')

将 /Somedate 文本节点(text())的内容([1])转换成 xs:date 类型(cast as xs:date?),然后与指定的日期进行比较。若相等则返回 1;若不相等则返回 0;若包含 NULL 则返回 NULL。

exist 实际应用

select * from tbl where [data].exist('/root/item[@id>3]')=1
select * from tbl where [data].exist('/root/item[text()=''itpow'''''']')=1

可以看出:

  • exist 是否匹配,是与 1 比较

  • 比较属性值用 propName、节点值用 text(),注意:判断用的 XPath取值用的 XPath 的不同,这里使用中括号将 @propName、text() 及后面的比较,括起来了,XPath 的话,text() 前面是斜杠,而这里不能有斜杠。

  • 数字比较,自动转换成数字。

  • 文本比较,1 个单引号替换为 4 个单引号,因为它先是放在比较值字符串中,再放在 exist 参数这个字符串中。

modify

SET @myDoc.modify('          
insert sql:variable("@newFeatures")          
into (/Root/ProductDescription/Features)[1] ')

表示将 @newFeatures 插入到 /Root/ProductDescription/Features。

再举一个更新表中 XML 字段某节点的例子:

update tblDatas 
	set data.modify('
		replace value of (/root/sitename/text())[1]
		with ''itpow''
	')
	where id=123

如上,将 id 为 123 的记录的 data 字段的 /root/sitename 节点值改为 itpow。

nodes

SELECT T.c.query('.') AS result FROM @xml.nodes('/Root/row') T(c)

将 xml 内容中所有的 /Root/row 节点以关系表格的形式返回,并存储在虚拟表 T 的字段 c 中,然后利用 T.c.query('.') 将 虚拟表 T 的字段 c 中的节点内容查询出来。

相关阅读

相关文章