学习之路十二：SQL Server操作XML以及遇到的问题
这几天项目业务需求需要对数据库中的一段XML进行操作，虽然以前看过几眼但是都忘记了，没印象了，所以这几天简简单单的学习了一下，于是把感悟与大家分享一下！
先吐槽一下，我总是对“create”和“declare”搞乱，昨天写存储过程直接写成“declare procedure P_GetInfo”这样的语句，结果执行报错了，找了有半个小时了还没有搞定，结果找Leader过来一看说你看看你定义存储过程的语法对不对，我愣了一下，惭愧了，人品弱爆了，以后不能粗心大意了，教训，不多说了，Let's go！
我XML的格式如下
1 <Canvas name="" designWidth="640" designHeigh = "100" > 2 <Button name="1" width="5" height="2" /> 3 <Button name="2" width="5" height="2" /> 4 <Button name="3" width="5" height="2" /> 5 <Button name="4" width="3" height="2" /> 6 <Button name="5" width="5" height="2" /> 7 <Button name="6" width="3" height="2" /> 8 <Button name="7" width="3" height="2" /> 9 <Button name="8" width="4" height="2" /> 10 <Button name="9" width="3" height="2" /> 11 <Button name="10" width="4" height="2" /> 12 <Button name="11" width="3" height="2" /> 13 <Button name="12" width="5" height="2" /> 14 <Button name="13" width="5" height="2" /> 15 <Button name="14" width="5" height="2" /> 16 <Button name="15" width="3" height="2" /> 17 <Button name="16" width="3" height="2" /> 18 <Button name="17" width="5" height="2" /> 19 </Canvas >
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 <Button name="15" width="3" height="2" />
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.cnblogs.com/guopeng/archive/2009/12/11/1621527.html
http://www.cnblogs.com/wuhong/archive/2011/04/15/2017281.html
http://www.cnblogs.com/gcb999/archive/2012/04/05/2433498.html
http://www.cnblogs.com/kingwangzhen/archive/2012/01/05/2313495.html
以同步至 → 程序猿个人文章目录索引 (一直更新中...)
