学习之路十二：SQL Server操作XML以及遇到的问题
这几天项目业务需求需要对数据库中的一段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') FROM devDTTSConfig
②value(xquery)
有获取属性值和获取节点值的功能！
★：获取节点中属性的值：
1 --查询xml中我所指定的属性值
3 --如果对节点中属性值的长度没有很好的把握的话，建议使用 → NVARCHAR(max)
4 SELECT Configuration.value('(Canvas/Button/@name)','NVARCHAR(4)') FROM devDTTSConfig
5 SELECT Configuration.value('(Canvas/Button/@width)','NVARCHAR(max)') FROM devDTTSConfig
6 SELECT Configuration.value('(Canvas/Button/@height)','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)','NVARCHAR(max)') = 5//只会筛选xml中Button节点中的第一个节点
4 SELECT * FROM devDTTSConfig WHERE Configuration.value('(Canvas/Button/@name)','NVARCHAR(max)') LIKE '%1%'
5 SELECT * FROM devDTTSConfig WHERE Configuration.value('(Canvas/Button/@height)','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 .
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.,
14 devDTTSConfig.ViewAs,
15 devDTTSConfig.,
16 devDTTSConfig.,
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 WHEREButtonTable.ButtonType IS NOT NULL
31 AND ButtonTable.ButtonNumber IS NOT NULL
32 AND ButtonTable.ButtonType.value(''(/Button/@objectNo)'', ''nvarchar(max)'') IS NOT NULL
33 AND ButtonTable.ButtonNumber.value(''(/Button/@buttonType)'', ''nvarchar(max)'') IS NOT NULL
34 ) AS T
35 INNER JOIN devDTTSConfig ONdevDTTSConfig.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
以同步至 → 程序猿个人文章目录索引 (一直更新中...)
页:
[1]