用户名:
密 码: 记住
您当前的位置:首页 > 网络编程 > sqlserver教程

SQLServer2005 XML在T-SQL中的应用

时间:2014-11-30  来源:互联网  作者:佚名

以下为引用的内容:

*

SQL(SQL Server培训 mySQL培训 )Server2005 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('' + REPLACE(@s,',','') + '' AS XML) x) a --将字串","换换为""并前后拼上,以用来构造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('' + REPLACE(@s,',','') + '' 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('' + REPLACE(@s,',','') + '' 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 ,b ,c ,dd ,ee ,aa ,f

*/

--c distinct-values

SELECT REPLACE(v,' ',',') FROM

(SELECT CAST('' + REPLACE(@s,',','') + '' 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" ?>

e0001

萧峰

e0002

段誉

e0003

王语嫣

e0003

张无忌

'

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)),'',case when type='1' then '-' else ',' end),'',''),1,6,type + ' | |') a

from

(

select left(a,1) type,

cast(

''

+

replace(

stuff(a,1,5,''),

case when left(a,1)=1 then '-' else ',' end,

''

)

+

''

AS XML

) x

from ta

) base

where x.value('

if (sql:column("base.type")="1") then

if(

(/item/text())[1]

and

(/item/text())[2]>sql:variable("@s")

)

then 1

else 0

else

count(//item[text()>sql:variable("@s")])

'

,

'int'

)>0

go

来顶一下
返回首页
返回首页
推荐资讯
从零开始学ASP.NET-基础篇第1/7页 从零开始学ASP.NET-基础篇第1/7页第一天 学习目的: 掌握最基本的Label、TextBox、Button控件
ASP.NET入门数据篇 ASP.NET入门数据篇对于网站编程的初学者来说,总是会上网找些源码来看,但久而
相关文章
栏目更新
栏目热门