学习之路十二：SQL Server操作XML以及遇到的问题

haixin3036

　　这几天项目业务需求需要对数据库中的一段XML进行操作，虽然以前看过几眼但是都忘记了，没印象了，所以这几天简简单单的学习了一下，于是把感悟与大家分享一下！


　　先吐槽一下，我总是对“create”和“declare”搞乱，昨天写存储过程直接写成“declare procedure P_GetInfo”这样的语句，结果执行报错了，找了有半个小时了还没有搞定，结果找Leader过来一看说你看看你定义存储过程的语法对不对，我愣了一下，惭愧了，人品弱爆了，以后不能粗心大意了，教训，不多说了，Let's go！
　　我XML的格式如下



1
2   
3   
4   
5   
6   
7   
8   
9   
10   
11   
12   
13   
14   
15   
16   
17   
18   
19
　　1.查询
　　①query(xquery)
　　有筛选和查询功能，最主要的还是筛选功能！
　　查找Button节点中属性“name”的值为15的需求：



1 --查找节点中的属性
2 --语法 → '路径(应该是绝对路径)[属性名 = "属性值"]' 等于 '/Canvas/Button[@name="45"]'
3 --注意单引号和双引号的使用，注意“[]”的使用，应该是一种语法把！
4 --Note:这样的方式只能查出当前节点中属性是否满足条件，不会返回所有
5 --应用场景：如果只是查找xml内部的话建议使用“query”进行查询
6 SELECT Configuration.query('Canvas/Button[@name="15"]') FROM devDTTSConfig  //devDTTSConfig是我存储xml的数据表
　　注意“@”符号的使用，以及把你用于过滤属性的值用双引号括起来（建议都要加上，如果是int型的可以不要加），这些都是个语法规则！
　　查询的结果为：



1
　　last：查找Button最后一个节点，相当于xml上面的第十七个节点！



1 SELECT Configuration.query('Canvas/Button[last()]') FROM devDTTSConfig 　　　　　　
　　
　　②value(xquery)
　　有获取属性值和获取节点值的功能！
　　★：获取节点中属性的值：



1 --查询xml中我所指定的属性值
3 --如果对节点中属性值的长度没有很好的把握的话，建议使用 → NVARCHAR(max)
4 SELECT Configuration.value('(Canvas/Button/@name)[1]','NVARCHAR(4)') FROM devDTTSConfig
5 SELECT Configuration.value('(Canvas/Button/@width)[1]','NVARCHAR(max)') FROM devDTTSConfig
6 SELECT Configuration.value('(Canvas/Button/@height)[1]','NVARCHAR(max)') FROM devDTTSConfig
　　这样就能把查询XML第一个节点中的属性值！
　　Note：说明一下“[]”的作用，相当于索引器，不过它是从“1”开始的，如果我把“name”后面中括号填“1”（以上面的数据为例），查出来的值就为“1”，如果填“2”，值就为“2”，以此类推！
　　★：对节点中的属性值进行筛选



1 --如果要对xml中的数据进行筛选，可以先获取属性值再进行筛选
2 --应用场景：如果是通过xml条件查找其它数据可以实现“value”进行查询
3 SELECT * FROM devDTTSConfig WHERE Configuration.value('(Canvas/Button/@width)[1]','NVARCHAR(max)') = 5  //只会筛选xml中Button节点中的第一个节点
4 SELECT * FROM devDTTSConfig WHERE Configuration.value('(Canvas/Button/@name)[1]','NVARCHAR(max)') LIKE '%1%'
5 SELECT * FROM devDTTSConfig WHERE Configuration.value('(Canvas/Button/@height)[1]','NVARCHAR(max)') > 211
　　总结：对于“value”查询，虽然比较灵活，但局限性比较大，它只能对某一个节点进行筛选，而不是在xml下所有相同节点的筛选，所以在大多数的情况下使用“query”进行筛选是一个很好的选择
　　
　　③exist
　　语法跟query类似，返回值为“1”或“0”，返回“1” → 存在，反之不存在！



1 SELECT Configuration.exist('Canvas/Button[@height=2]') FROM devDTTSConfig
　　
　　2.遇到的问题
　　项目需要对xml中节点的属性值进行过滤，而这些属性值都是动态指定的，一开始就采用下面的这种方式如：



1 SELECT Configuration.query('Canvas/Button[@height="'+@height+'"]') FROM devDTTSConfig
　　采用的是嵌入参数的方式，但是一直报错，说必须输入的为一个“string”类型的字符串！
　　第一种方法：一种比较简单的方式，使用 → sql:variable("参数名")，但这种很少人知道，所以基本不会用，详细请看：SQL 中操作XML类型数据



1 SELECT Configuration.exist('Canvas/Button[@height= sql:variable("@height") ]') FROM devDTTSConfig
　　第二种方法：最后还是使用拼接字符串的方式搞定的（leader教我的，o(︶︿︶)o ）！
　　所以说拼接字符串在很大程度上解决了很多难题，不过拼接字符串比较头疼是单引号处理的问题，不懂的人要搞的累死才能写对，哎，我就是属于这种人！



1 CREATE PROC [dbo].[sp_GetTsConfig]
2 (
3     @buttonTypeNo        NVARCHAR(10),
4     @objectNo            NVARCHAR(10)
5 )
6 AS
7     EXECUTE
8     ('
9     SELECT devDTTSConfig.ID,
10            devDTTSConfig.TSConfigNumber,
11            devDTTSConfig.ScreenName,
12            devDTTSConfig.TSType,
13            devDTTSConfig.[Template],
14            devDTTSConfig.ViewAs,
15            devDTTSConfig.[Language],
16            devDTTSConfig.[Status],
17            devDTTSConfig.UpUser,
18            devDTTSConfig.UpDT,
19            devDTTSConfig.Calc_No,
20            T.configuration
21     FROM   (
22                SELECT ButtonTable.TSConfigNumber,
23                       ButtonTable.ButtonType AS configuration
24                FROM   (
25                           SELECT TSConfigNumber,
26                                  Configuration.query(''(/Canvas/Button[@buttonType="'+@buttonTypeNo+'"])'') AS ButtonType,
27                                  Configuration.query(''(/Canvas/Button[@objectNo="'+@objectNo+'"])'') AS ButtonNumber
28                           FROM   devDTTSConfig
29                       ) AS ButtonTable
30                WHERE  ButtonTable.ButtonType IS NOT NULL
31                       AND ButtonTable.ButtonNumber IS NOT NULL
32                       AND ButtonTable.ButtonType.value(''(/Button/@objectNo)[1]'', ''nvarchar(max)'') IS NOT NULL
33                       AND ButtonTable.ButtonNumber.value(''(/Button/@buttonType)[1]'', ''nvarchar(max)'') IS NOT NULL
34            ) AS T
35            INNER JOIN devDTTSConfig ON  devDTTSConfig.TSConfigNumber = T.TSConfigNumber
36     Order by devDTTSConfig.UpDT desc
37     ')
　　
　　关于XML的添加，删除，修改可以参考以下文章：
　　http://www.iyunv.com/guopeng/archive/2009/12/11/1621527.html
　　http://www.iyunv.com/wuhong/archive/2011/04/15/2017281.html
　　http://www.iyunv.com/gcb999/archive/2012/04/05/2433498.html
　　http://www.iyunv.com/kingwangzhen/archive/2012/01/05/2313495.html
　　以同步至 → 程序猿个人文章目录索引 (一直更新中...)