学习之路十二: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 

以同步至 → 程序猿个人文章目录索引 (一直更新中...)

posted @ 2012-05-09 19:05  TimYang  阅读(2146)  评论(6编辑  收藏  举报