分类
数据库 编程开发

SqlServer查询字符串聚集 for xml

sqlserver提供了一系列将查询结果的其中一列的聚集函数,不如Sum(),Avg()等等,这些都是针对数据聚集的函数。那么有没有字符串聚集的函数呢?

没有这样的内置函数,一直以来只能通过自定义函数来实现。

但是sqlserver05以后有了一个比较完美的解决办法,使用for xml path来取以逗号分隔的多行数据的应用
for xml有两个修饰符,还有两个修饰符,一共有四种,分别是:
1、RAW
2、AUTO
3、PATH
4、EXPLICIT
基本上是按照功能的强弱正序排列的,闲话少说,直接看代码:

--RAW
--SELECT CorpCode FROM dbo.Kvp_CorpInfo FOR XML AUTO('')
--仅FOR XML 的RAW 或PATH 模式允许行标记名称。
SELECT CorpCode FROM dbo.Kvp_CorpInfo FOR XML RAW('')
--行标记省略(空的行标记名称)不能与以属性为中心的FOR XML 序列化一起使用。?
SELECT CorpCode FROM dbo.Kvp_CorpInfo FOR XML RAW(''),elements

SELECT TOP 100 productid,productname
FROM dbo.Bas_ProductInfo
FOR XML RAW, elements;
--FOR XML RAW, Type;--结果命名不同.msdn:您可以选择性地指定TYPE 指令将结果作为xml 类型进行检索。TYPE 指令不会更改结果的内容。只影响结果的数据类型。

--Auto
SELECT TOP 1 productid,productname
FROM dbo.Bas_ProductInfo A
FOR XML AUTO, elements;

--交叉数据的嵌套顺序
SELECT OrderInfo.SalesOrderID,OrderInfo.TotalAmount,OrderList.SalesOrderID,OrderList.SalesOrderListId,OrderList.ProductId,OrderList.SelfPrice
FROM dbo.Ope_SalesOrderRecord OrderInfo,dbo.Ope_SalesOrderList OrderList
WHERE OrderInfo.SalesOrderID=OrderList.SalesOrderID
FOR XML AUTO,elements

SELECT OrderList.SalesOrderID,OrderList.SalesOrderListId,OrderList.ProductId,OrderList.SelfPrice,OrderInfo.SalesOrderID,OrderInfo.TotalAmount
FROM dbo.Ope_SalesOrderRecord OrderInfo,dbo.Ope_SalesOrderList OrderList
WHERE OrderInfo.SalesOrderID=OrderList.SalesOrderID
FOR XML AUTO

/*
在创建父元素的过程中,会对每个即将创建的父节点进行比较,如果值不同,将向XML 添加新的父元素,值相同则比较属性,以此类推。
在比较这些列值时,如果要比较的任何列是text、ntext、image 或xml 类型,即使它们的值可能相同,FOR XML 也将认为它们是不同的,并且不对其进行比较。这是因为不支持大型对象的比较。这些元素将被添加到每个选定行的结果中。请注意,会比较(n)varchar(max) 和varbinary(max) 类型的列。
*/

--EXPLICIT 
/*
[适用于复杂结构的xml]
常见格式:
	ElementName!TagNumber!AttributeName!Directive
Directive:
如果未指定Directive 和AttributeName(例如Customer!1),则暗含一个element 指令(如Customer!1!!element),并且列数据包含在ElementName 中。	
一种用途是将值编码为ID、IDREF 和IDREFS。可以将ID、IDREF 和IDREFS 关键字指定为Directives。这些指令将覆盖属性类型。这使您能够创建文档内链接。
另外hide、element、elementxsinil、xml、xmltext 和cdata
除不发生实体编码外,xml 指令与element 指令相同。
http://msdn.microsoft.com/zh-cn/library/ms189068
*/

SELECT  
1 as tag,
null as parent,
A.CorpCode AS [人员!1!姓名],NULL AS[人员信息!2!年龄!xml] FROM dbo.Kvp_CorpInfo A WHERE CorpCode<>''
UNION
SELECT 
2 AS tag,
1 AS parent,
A.CorpCode,B.CorpId FROM dbo.Kvp_CorpInfo A,dbo.Kvp_CorpInfo B WHERE A.CorpCode<>'' AND a.CorpCode=b.CorpCode
order by [人员!1!姓名],tag--此处排序以供节点嵌套
FOR XML EXPLICIT

--PATH
SELECT 2+2 FOR XML PATH--值,xml:<row>4</row>
SELECT 2+2 '@T' FOR XML PATH--属性,xml:<row T="4" />
SELECT 2+2 T FOR XML PATH--节点,xml:<row><T>4</T></row>
SELECT 2+2 'T/F' FOR XML PATH--多级节点,xml:<row><T><F>4</F></T></row>

--名称指定为通配符的列
/*如果指定的列名是一个通配符(*),则插入此列的内容时就像没有指定列名那样插入。如果此列不是xml 类型的列,则此列的内容将作为文本节点插入*/
SELECT CorpCode '@Code',CorpCode '*',':' '*',CorpName '*'
FROM dbo.Kvp_CorpInfo WHERE CorpCode<>''
FOR XML PATH
/*
<row Code="0021">0021:北京**有限公司 </row>
<row Code="0006">0006:南京**有限公司   </row>
<row Code="0024">0024:上海**有限公司 </row>
<row Code="009A">009A:DCgou</row>
<row Code="0046">0046:北京**供应链服务有限公司</row>
<row Code="00X8">00X8:广东**有限公司</row>
*/

SELECT RuleID,RuleName,Param.query('/Rule') '*'
FROM dbo.Sys_RuleInfo WHERE RuleID=101150000000000011
FOR XML PATH

--列名为XPath 节点测试的列text()/comment()/node()
SELECT 2+2 'text()' FOR XML PATH--=writetext();
SELECT 2+2 'comment()' FOR XML PATH--=writecomment();
SELECT 2+2 'node()' FOR XML PATH--=*

--data()
SELECT corpcode 'data()' FROM dbo.Kvp_CorpInfo WHERE CorpCode<>'' FOR XML PATH('')--xml:0021 0006 0024 009A 0046 00X8
SELECT * FROM Sys_RuleInfo 

--使用path,获取以分隔符分隔的聚集字符串
SELECT CorpCode+',' FROM dbo.Kvp_CorpInfo WHERE CorpCode<>'' FOR XML PATH('')--xml:0021,0006,0024,009A,0046,00X8,
SELECT ''''+CorpCode+''',' FROM dbo.Kvp_CorpInfo WHERE CorpCode<>'' FOR XML PATH('')--xml:'0021','0006','0024','009A','0046','00X8',
<span style="color: #000000;"> </span>

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注