SQLServer 2005 XML 在 T-SQL 查询中的典型应用[转]

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

前言:
此文只讲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
复制代码

待续。。




本文转自钢钢博客园博客,原文链接:http://www.cnblogs.com/xugang/archive/2011/10/18/2216296.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
4月前
|
SQL 存储 测试技术
SQL在构建系统中的应用:关键步骤与技巧
在构建基于数据库的应用系统时,SQL(Structured Query Language)作为与数据库交互的核心语言,扮演着至关重要的角色
|
3月前
|
SQL 数据库
如何应用SQL约束条件?
【10月更文挑战第28天】如何应用SQL约束条件?
111 11
|
4月前
|
XML 存储 数据库
XML在数据库中有哪些应用?
【10月更文挑战第17天】XML在数据库中有哪些应用?
86 2
|
4月前
|
SQL Oracle 关系型数据库
SQL语言的主要标准及其应用技巧
SQL(Structured Query Language)是数据库领域的标准语言,广泛应用于各种数据库管理系统(DBMS)中,如MySQL、Oracle、SQL Server等
127 9
|
4月前
|
SQL 数据库 数据库管理
数据库SQL函数应用技巧与方法
在数据库管理中,SQL函数是处理和分析数据的强大工具
|
3月前
|
SQL 监控 安全
员工上网行为监控软件:SQL 在数据查询监控中的应用解析
在数字化办公环境中,员工上网行为监控软件对企业网络安全和管理至关重要。通过 SQL 查询和分析数据库中的数据,企业可以精准了解员工的上网行为,包括基础查询、复杂条件查询、数据统计与分析等,从而提高网络管理和安全防护的效率。
52 0
|
4月前
|
SQL 存储 数据库
SQL在构建系统中的应用:关键要素与编写技巧
在构建基于数据库的系统时,SQL(Structured Query Language)扮演着至关重要的角色
|
4月前
|
SQL 关系型数据库 数据库
SQL数据库:核心原理与应用实践
随着信息技术的飞速发展,数据库管理系统已成为各类组织和企业中不可或缺的核心组件。在众多数据库管理系统中,SQL(结构化查询语言)数据库以其强大的数据管理能力和灵活性,广泛应用于各类业务场景。本文将深入探讨SQL数据库的基本原理、核心特性以及实际应用。一、SQL数据库概述SQL数据库是一种关系型数据库
153 5
|
4月前
|
SQL 存储 Oracle
Oracle数据库SQL语句详解与应用指南
在数字化时代,数据库已成为各类企业和组织不可或缺的核心组件。Oracle数据库作为业界领先的数据库管理系统之一,广泛应用于各种业务场景。掌握Oracle数据库的SQL语句是数据库管理员、开发人员及运维人员的基本技能。本文将详细介绍Oracle数据库SQL语句的基本概念、语法、应用及最佳实践。一、Or
126 3
|
4月前
|
SQL 存储 数据挖掘
SQL Server 日期格式查询详解
SQL Server 日期格式查询详解
300 2