使用 SQL Server 2008 数据类型－xml 字段类型参数进行数据的批量选取或删除数据

使用 SQL Server 2008 数据类型－xml 字段类型参数进行数据的批量选取或删除数据

SQL, xml, 字段, Server, 批量

我们经常有这样的需求，批量的删除或者选取大量的数据，有非常多的Id值，经常使用in条件查询，如果你使用拼接字符串的方式，可能遭遇SQL语句的长度限制4000个字符。可以使用XML的参数类型来解决。例如下面这个例子。

view source

print?

1.DECLARE @Ids xml 

2.set @Ids ='<Id>12</Id><Id>1</Id>' 

3.select * from  Employees Where EmployeeID in 

4.( 

5.    SELECT ParamValues.ID.value('.','int') as Id 

6.    FROM @Ids.nodes('/Id') as ParamValues(ID) 

7.)

写个函数来生成Id的xml格式用C#非常容易：

view source

print?

01.public sealed class PrimaryKeyXMLFormatter

02.{ 

03.        public static string FormatXmlForIdArray<T>(Collection<T> idsToList) 

04.        { 

05.            StringBuilder xmlString = new StringBuilder(); 

06.            for (int i = 0; i < idsToList.Count; i++) 

07.            { 

08.                xmlString.AppendFormat("<Id>{0}</Id>", idsToList[i]); 

09.            } 

10.            return xmlString.ToString(); 

11.        } 

12.}

从SQL Server 2005新增了XML字段，并且增加了SQL语句直接处理XML字段的功能，也就是说可以直接把 xml 内容存储在该字段中，并且 SQL Server 会把它当作 xml 来对待，而不是当作 varchar 来对待。通过使用SQL语句可以直接获取存放再XML字段中的数据的行集，之后可以使用DataSet或DataTable进行数据处理，当需要写入数据到XML字段时，我们可以使用Modify()函数来实现直接更新数据库。

SQL Server 中以 Unicode(UTF-16) 来存储 XML 数据。
XML 字段最多可存储 2G 的数据。
可以像插入字符串一样向 XML 字段写入内容。
当在 xml 数据类型实例中存储 XML 数据时，不会保留 XML 声明（如 <?xml version='1.0'?>）。
插入的 xml 内容的属性的顺序可能会与原 xml 实例的顺序变化。
不保留属性值前后的单引号和双引号。
不保留命名空间前缀。
可以对 XML 字段中的 XML 内容建立索引 http://blog.csdn.net/tjvictor/archive/2009/07/22/4370771.aspx。
可以对 XML 字段中的 XML 内容建立约束，比如 age 节点必须大于等于 18。
可以通过创建架构来对 XML 进行类型化，比如让 xml 内容的 <user> 节点下面必须有 <fullname> 节点。

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

query

view source

print?

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

返回 Features 节点及其子节点。

value

view source

print?

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

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

exists

view source

print?

1.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。

modify

view source

print?

1.SET @myDoc.modify('           

2.insert sql:variable("@newFeatures")           

3.into (/Root/ProductDescription/Features)[1] ')

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

nodes

view source

print?

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

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

Passing lists to SQL Server 2005 with XML Parameters
http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx