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

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

DECLARE @Ids xml

set @Ids ='<Id>12</Id><Id>1</Id>' 
select * from  Employees Where EmployeeID in    
(     
    SELECT ParamValues.ID.value('.','int') as Id     
    FROM @Ids.nodes('/Id') as ParamValues(ID)     
)

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

public sealed class PrimaryKeyXMLFormatter
{    
        public static string FormatXmlForIdArray<T>(Collection<T> idsToList)     
        {     
            StringBuilder xmlString = new StringBuilder(); 
            for (int i = 0; i < idsToList.Count; i++)    
            {     
                xmlString.AppendFormat("<Id>{0}</Id>", idsToList[i]);     
            } 
            return xmlString.ToString();    
        }     
}

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

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

返回 Features 节点及其子节点。

value

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

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

exists

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

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

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

nodes

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏everhad

Android代码分析工具lint学习

1 lint简介 1.1 概述 lint是随Android SDK自带的一个静态代码分析工具。它用来对Android工程的源文件进行检查,找出在正确性、安全、性...

29290
来自专栏闪电gogogo的专栏

Git学习笔记

此篇笔记是学习参考廖雪峰老师的Git教程,附上学习网址:https://www.liaoxuefeng.com/wiki/0013739516305929606...

27280
来自专栏everhad

[翻译][架构设计]The Clean Architecture

原文地址:The Clean Architecture The Clean Architecture ? Over the last several years...

85960
来自专栏数据库

试用ODU软件恢复corrupt block

Oracle的数据恢复处理,有各种方法工具支持,在这方面,我算是一个新手,也是处于不断的学习中。 业界有一些著名的恢复软件,简单罗列一下, 1. Oracle ...

34950
来自专栏everhad

笔记:Binder通信机制

TODO: 待修正 Binder简介 Binder是android系统中实现的一种高效的IPC机制,平常接触到的各种XxxManager,以及绑定Service...

32080
来自专栏地方网络工作室的专栏

Python3 初学实践案例(8)使用 sqlite3 数据库存储生成的密码,prettytable 的使用

Python3 初学实践案例(8)使用 sqlite3 数据库存储生成的密码,prettytable 的使用 在前面我用 python 脚本实现的 cli 版本...

30450
来自专栏everhad

札记:Java异常处理

异常概述 程序在运行中总会面临一些“意外”情况,良好的代码需要对它们进行预防和处理。大致来说,这些意外情况分三类: 交互输入 用户以非预期的方式使用程序,比如...

28780
来自专栏数据库

数据库设计技巧:一个字符串细节处理解决递归查询问题

问题提出 许多人在做开发时都会遇到这样的case,比如:需要维护一个部门层级结构,每个部门都要存储一个上级部门的id,记做parent_id, 当查询一个部门的...

245100
来自专栏闪电gogogo的专栏

浅读K-means

百度百科释义为   K-means算法是硬聚类算法,是典型的基于原型的目标函数聚类方法的代表,它是数据点到原型的某种距离作为优化的目标函数,利用函数求极值的方法...

21360
来自专栏everhad

虾扯蛋:Android View动画 Animation不完全解析

本文结合一些周知的概念和源码片段,对View动画的工作原理进行挖掘和分析。以下不是对源码一丝不苟的分析过程,只是以搞清楚Animation的执行过程、如何被周期...

28390

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励