SQLServer2005 XML在T-SQL查询中的典型应用

本文介绍 SQL Server 2005 中 XML 数据类型的实用操作技巧,包括字符串拆分、去重、列值聚合等功能,并通过具体示例展示了如何使用 XQuery 和 XML 方法提高 T-SQL 编程效率。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

原帖:http://blog.youkuaiyun.com/htl258/archive/2009/04/13/4071123.aspx

 

/*
SQLServer2005 XML在T-SQL查询中的典型应用

整理:fcuandy
时间:2008.11.7

前言:
    此文只讲xml数据类型及相应的一些操作方法在解决日常T-SQL编程中的一些应用,而避开xml modify,
xml schema,xml索引,命名空间等这些语法性或者生硬的一些问题(这些语法您可以查联机丛书),即此文主要
讲以xml的一些操作特性及xquery去解决编程问题.

Tags:
    xquery ,FLWOR迭带 ,sql:column ,sql:variable ,nodes ,value ,query ,xpath ,xquery function, if, 聚合函数, xs:function等

典型应用举例:
*/


-- (1)
--
====================================================================
--
拆分
DECLARE @s VARCHAR ( 100 )
SET @s = ' a,b,c,dd,ee,f,aa,a,aa,f '

-- 常规做法(sql2000常用),以一split函数拆分串为表类型结构,如
--
SELECT * FROM dbo.split(@s,',') a
--
当然,也可能是循环去拆分,或者以一输助表的identity列利用charindex等函数拿identity列值与','的位置匹配实现拆分
--
这些做法,roy_88及本人以前都整理过,不再累赘,可见推荐贴。即便 是xml法,也贴过多次,下面一笔带过

-- XML做法:
SELECT b.v FROM
    (
SELECT CAST ( ' <r> ' + REPLACE ( @s , ' , ' , ' </r><r> ' ) + ' </r> ' AS XML) x) a   -- 将字串","换换为"</r><r>"并前后拼上<r>,</r>以用来构造xml串
CROSS APPLY
    (
SELECT v = t.x.value( ' . ' , ' VARCHAR(10) ' ) FROM a.x.nodes( ' //r ' ) AS t(x) ) b  -- 使用 xml.nodes函数将xml串拆分为行
/*

a
b
c
dd
ee
f
aa
a
aa
f
*/


-- (2)
--
====================================================================
--
去重,@s中出现的元素,重复的只要一个,希望结果为 'a,b,c,dd,ee,f'
--
常规做法,循环或函数,或临时表拆后distinct
--
XML做法:
--
a.在(1)的基础上进行

;
WITH fc AS    -- 定义cte命名,将@s转换为一个表结构
(
   
SELECT DISTINCT b.v v
           
FROM
                (
SELECT CAST ( ' <r> ' + REPLACE ( @s , ' , ' , ' </r><r> ' ) + ' </r> ' AS XML) x) a
           
CROSS APPLY
                (
SELECT v = t.x.value( ' . ' , ' VARCHAR(10) ' ) FROM a.x.nodes( ' //r ' ) AS t(x) ) b
)
-- 对这个表利用xml方法进行行值拼接
SELECT STUFF (b.v.value( ' /r[1] ' , ' varchar(100) ' ), 1 , 1 , '' )
   
FROM
    (
SELECT v = ( SELECT ' , ' + v FROM fc FOR XML PATH( '' ),ROOT( ' r ' ),TYPE)) b
/*
a,aa,b,c,dd,ee,f
*/

-- b FLWOR语句 + T-SQL组合:
SELECT STUFF (v, 1 , 1 , '' ) FROM
    (
SELECT CAST ( ' <r> ' + REPLACE ( @s , ' , ' , ' </r><r> ' ) + ' </r> ' AS XML) x) a
CROSS APPLY
    (
SELECT x = ( SELECT t.x.value( ' . ' , ' varchar(10) ' ) v,idx = ROW_NUMBER() OVER ( ORDER BY GETDATE ()) FROM a.x.nodes( ' //r ' ) AS t(x) FOR XML PATH( ' r ' ),TYPE)) b -- 利用row_number得到唯一idx
CROSS APPLY
    (
SELECT v = CAST (b.x.query( ' for $r in //r where count(//r[v=$r/v and idx<$r/idx])=0 return concat(",",xs:string($r/v[1])) ' ) AS VARCHAR ( MAX ))) c  -- 类似count计数法,取得v相同的节点集idx值最小的节点,原型为:
--
SELECT * FROM tb a WHERE 1>(SELECT COUNT(*) FROM tb WHERE v=a.v AND id<a.id)
/*

a ,b ,c ,dd ,ee ,aa ,f
*/


-- c distinct-values
SELECT REPLACE (v, ' ' , ' , ' ) FROM
    (
SELECT CAST ( ' <r> ' + REPLACE ( @s , ' , ' , ' </r><r> ' ) + ' </r> ' AS XML) x) a
CROSS APPLY
    (
SELECT CAST (a.x.query( ' distinct-values(//r) ' ) AS VARCHAR ( MAX )) v) b  -- 直接调用distinct-values函数来操作
/*

a,b,c,dd,ee,f,aa
*/


-- 导入去重, last() , position()

DECLARE    @doc   xml
SET    @doc    = ' <?xml version="1.0" encoding="gb2312" ?>
<employees>
    <employee>
        <empid>e0001</empid>
        <name>萧峰</name>
    </employee>
    <employee>
        <empid>e0002</empid>
        <name>段誉</name>
    </employee>
    <employee>
        <empid>e0003</empid>
        <name>王语嫣</name>
    </employee>
    <employee>
        <empid>e0003</empid>
        <name>张无忌</name>
    </employee>
</employees>
'
create table people2
(
    personid
varchar ( 10 primary key ,
    name
varchar ( 20 )
)

INSERT people2
SELECT DISTINCT b. * FROM
    (
SELECT x = @doc .query( ' for $e in //employee  return  //employee[empid = $e/empid][last()] ' )) a  -- FLWOR时,用当前节点去//emploee节点集中找节点集中empid等于当前节点的empid, 在找到的集合中取最后一个利用last()函数
CROSS APPLY
    (
SELECT id = t.x.value( ' empid[1] ' , ' varchar(100) ' ),name = t.x.value( ' name[1] ' , ' varchar(100) ' ) FROM a.x.nodes( ' //employee ' ) AS t(x)) b

SELECT * FROM people2
/*
e0001    萧峰
e0002    段誉
e0003    张无忌
*/
GO
drop table people2
GO
-- 同组一选多,也可应用此方法,不过没有必要,就不再累赘了。


-- (3)
--
====================================================================
--
列名,列值相关
--
a,按行聚合
declare @t table (Sname nvarchar ( 5 ),  V1 float ,    V2 float ,    V3 float ,      V4 float ,    V5 float ,      V6 float )
insert @t select N ' 张三 ' ,    0.11 , 0.21 , 0.29 0.32 ,   0.11 ,    0.08
insert @t select N ' 李四 ' ,    0.01 , 0.61 , 0.21 0.73 ,   0.21 ,    0.12
insert @t select N ' 张五 ' ,    0.31 , 0.21 , 0.23 0.33 ,   0.91 ,    0.65
insert @t select N ' 张六 ' ,    0.59 , 0.11 0.26 0.13 ,    0.01 ,    0.15

select b. * from
    (
select x = cast (( select * from @t for xml path( ' r ' )) as xml)) a
cross apply
    (
       
select name = x.query( ' ./Sname/text() ' ),v = x.query( ' max(./*[local-name(.)!="Sname"]) ' ) from a.x.nodes( ' //r ' ) as t(x) 
       
-- r为二级节点(因为文档本身无根节点,即为每项的顶级节点)即为一个r节点表示一条记录. r下级节点,每个表示一个列,因为列名未知,所以用/*匹配所有节点,因为name为区别列,不参与聚合运算,故用local-name取得来过滤
    ) b

/*
张三    0.32
李四    0.73
张五    0.91
张六    0.59
*/

-- b ,由值引到取列
if not object_id ( ' T1 ' ) is null
   
drop table T1
GO
Create table T1( [ tId ] int , [ tName ] nvarchar ( 4 ))
Insert T1
select 1 ,N ' zhao ' union all
select 2 ,N ' qian ' union all
select 3 ,N ' sun '
Go
-- > --> 借且(Roy)生成測試數據

if not object_id ( ' T2 ' ) is null
   
drop table T2
Go
Create table T2( [ tId ] int , [ zhao ] nvarchar ( 1 ), [ qian ] nvarchar ( 1 ), [ sun ] nvarchar ( 1 ))
Insert T2
select 1 ,N ' a ' ,N ' b ' ,N ' c ' union all
select 2 ,N ' d ' ,N ' e ' ,N ' f ' union all
select 3 ,N ' g ' ,N ' h ' ,N ' i '
Go


SELECT c.tid,c.tName,v FROM t1 c
CROSS APPLY
    (
SELECT x = ( SELECT * FROM t2 WHERE tid = c.tid FOR XML PATH( ' r ' ),TYPE)) a
CROSS APPLY
    (
SELECT v = t.x.query( ' ./*[local-name(.)=xs:string(sql:column("c.tName")) ]/text() ' )
       
FROM a.x.nodes( ' //r ' ) AS t(x)
    ) b

/*
1    zhao    a
2    qian    e
3    sun    i
*/


-- c, 列名,列值,与系统表

CREATE TABLE tb(f1 INT ,f2 INT ,x INT ,z INT ,d INT ,ex INT ,dd INT ,vv INT )
INSERT tb SELECT 1 , 2 , 3 , 5 , 11 , 3 , 2423 , 33
GO
SELECT * FROM tb
GO
SELECT name,v FROM
  (
SELECT name FROM sys.columns WHERE object_id = object_id ( ' tb ' , ' u ' ) ) a
CROSS JOIN
  (
SELECT x = ( SELECT * FROM tb FOR XML PATH( ' r ' ),TYPE)) b
CROSS APPLY
(
SELECT v = t.x.query( ' ./*[local-name(.)=xs:string(sql:column("a.name")) ]/text() ' ) FROM b.x.nodes( ' //r ' ) AS t(x) ) c
/*
f1    1
f2    2
x    3
z    5
d    11
ex    3
dd    2423
vv    33
*/
GO
DROP TABLE tb
GO

-- (4)
--
一些综合计算
--
以下表 ta.a值 yyyymmdd-yyyymmdd表连续时间段,","表单个日期
If object_id ( ' ta ' , ' u ' ) is not null
   
Drop table ta
Go
Create table ta(a varchar ( 100 ))
Go
Insert into ta
select ' 1 ¦ ¦20080101-20080911 '
union all
select ' 2 ¦ ¦20080101,20080201,20080301,20080515,20080808 '
union all
select ' 3 ¦ ¦20080101,20080201,20080301,20080515,20081108 '
Go

declare @s varchar ( 8 )
select @s = convert ( varchar ( 8 ), getdate (), 112 )

select stuff ( replace ( replace ( cast (x as varchar ( 1000 )), ' </item><item> ' , case when type = ' 1 ' then ' - ' else ' ' end ), ' </item> ' , '' ), 1 , 6 ,type + ' ¦ ¦ ' ) a
   
from
    (
       
select left (a, 1 ) type,
           
cast (
                   
' <item> '
                   
+
                   
replace (
                       
stuff (a, 1 , 5 , '' ),
                       
case when left (a, 1 ) = 1 then ' - ' else ' ' end ,
                       
' </item><item> '
                        )
                   
+
                   
' </item> '
               
AS XML
                ) x
       
from ta
    ) base

   
where x.value( '
            if (sql:column("base.type")="1") then
                if(
                    (/item/text())[1]<sql:variable("@s")
                    and
                    (/item/text())[2]>sql:variable("@s")
                )
                then 1
                else 0
            else
                count(//item[text()>sql:variable("@s")])
           
'
            ,
           
' int '
            )
> 0
go

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值