SQLxml 常见用法

   最近深入研究了下sql xml 的常见用法，顺便写个总结。
当然和以往一样还是原创少，抄袭多- -嘛，，只是总结而已，请多多见凉，其中大量参考 近身剪  老大文章-0-。
详细请参考
apply 关键字的用法

http://msdn.microsoft.com/zh-cn/library/ms175156.aspx

XQuery查询

http://blog.csdn.net/jinjazz/archive/2009/08/13/4443585.aspx

For XML子句

http://msdn.microsoft.com/zh-cn/library/ms190922.aspx

我这里主要总结下关于常用的地方，
使用案例1：
当需要把多行字符串按一定规则汇总，连接时 (多行單字節字符串匯總)：

需要把下面表
ID                   NAME
-------------------- --------------------
1                    aaa
1                    bbb
1                    ccc
2                    ddd
2                    eee
-------------------- -------------------
转换为下面效果是
id                   abc
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                    aaa,bbb,ccc
2                    ddd,eee

 

---创建范例-------------------------------------- set nocount on if (object_id ('test' ) is not null ) drop table test create table test(ID varchar(20),NAME varchar(20)) insert into test select '1','aaa' insert into test select '1','bbb' insert into test select '1','ccc' insert into test select '2','ddd' insert into test select '2','eee' go select * from test go ---------------------------------------------------------------------- select id,cast (replace ([value] , '</v><v>' , ',' ) as xml ). value ('.' , 'varchar(max)' ) as abc from (select distinct id from test)a OUTER APPLY( select value=( select name as v from test where id = A.id for xml path ('' ))) b go

 

 

使用案例2：
当需要把一个字段中，进行按一定规则的拆分行为(多行單字節字符串拆分)，比如上例中进行反向操作

 

 

--创建范例-------------------------------------- set nocount on if (object_id ('test' ) is not null ) drop table test create table test(ID varchar(20),NAME varchar(50)) insert into test select '1','aaa,bbb,ccc' insert into test select '2','ddd,eee' select a.id,[NAME] = cast (replace (b.[NAME] , '</v><v>' , ',' ) as xml ). value ('.' , 'varchar(max)' ) from ( select ID ,convert (xml , '<v>' + replace (a.[NAME] , ',' , '</v><v>' )+ '</v>' ) as [NAME] from test a ) as a outer apply ( select t.c.value ('.' , 'varchar(max)' ) AS [NAME] from a.[NAME].nodes ('//v') AS t(c) ) as b

 

總結：

以上兩种情況，主要就是

1、當需要把一個字段進行 字符串拆分爲多行時

先使用

convert  (xml , '<v>' + replace  (a.[NAME] , ',' , '</v><v>' )+ '</v>' )

將需要拆分字段内容，轉換為xml格式

然後使用

outer  apply

    ( 
       select t.c.value ('.' , 'varchar(max)' ) AS [NAME] from a.[NAME].nodes ('//v') AS t(c) 
    )

對每一行數據，進行 提取單行(a.[NAME].nodes ('//v')),並獲取單行中，每個需要拆分的值t.c.value ('.' , 'varchar(max)' )

這樣就可以實現最終單行拆分多行的目的。

 

2、當需要把多行的一個字段，進行匯總合併時

先使用

select value=( 
                    select name as v from test 
                    where id = A.id 
                    for xml  path ('' ))

按邏輯將需要合併的行獲取，並使用for xml <path ('' )作用為清除xml的最外層標示> 合併在一個值中

通過outer  apply

傳遞給(select  distinct id from test)a 實現合併

最後通過

cast (replace ([value] , '</v><v>' , ',' ) as xml ). value ('.' , 'varchar(max)' ) as abc

將每行的xml格式的字段，轉換為普通同的，使用','連接的字段

這樣就可以實現把一個字段的多行，進行匯總合併的目的。

 

以上就是我碰到的，出現情况比较多，且如果使用sqlxml解决起来比较方便的两种案例

以后若碰到有其他使用到xml我再进行补充，