SQL Server For XML PATH使用

 For XML Path 语句可以将查询的数据集生成XML格式.

 通过简单的XPath语法来允许用户自定义嵌套的XML结构、元素、属性值.

基础语法： select * from tablename for xml path ('node name' )  (可以自定义XML的节点名称).

 

1.创建测试数据表和数据的脚本

--1.Create table
if exists (select 1 from  sysobjects where  id = object_id('tbOrder') and type = 'U')
DROP TABLE tbOrder
go

CREATE TABLE tbOrder
(
Id INT PRIMARY KEY IDENTITY(1,1),
OrderCode VARCHAR(20) NOT NULL,
TotalAmount DECIMAL(18,2),
OrderDate DATETIME,
OrderUser VARCHAR(20)
)

-- Insert data
INSERT INTO tbOrder(OrderCode,TotalAmount,OrderDate,OrderUser)VALUES('201301030001',500.00,GETDATE(),'user1')
INSERT INTO tbOrder(OrderCode,TotalAmount,OrderDate,OrderUser)VALUES('201301070001',600.00,GETDATE(),'user2')
INSERT INTO tbOrder(OrderCode,TotalAmount,OrderDate,OrderUser)VALUES('201301050001',300.00,GETDATE(),'user1')
INSERT INTO tbOrder(OrderCode,TotalAmount,OrderDate,OrderUser)VALUES('201301040001',1200.00,GETDATE(),'user3')
INSERT INTO tbOrder(OrderCode,TotalAmount,OrderDate,OrderUser)VALUES('201301030001',300.00,GETDATE(),'user2')

 

2. XML Path用法

(1)直接将数据集生成XML格式，并自定义节点名称为Order

SELECT * FROM dbo.tbOrder FOR XML  PATH ('Order')

 

(2)利用XML Path 进行逗号分隔字符串

SELECT OrderCode + ',' FROM tbOrder FOR XML PATH('')
--或者
DECLARE @str VARCHAR(2000)
SET @str = ''
SELECT @str = @str + OrderCode + ',' FROM tbOrder
PRINT @str

输出的结果

201301030001,201301070001,201301050001,201301040001,201301030001,

 

 (3)利用XML Path分组合并数据

SELECT s.OrderUser ,LEFT(b,LEN(b)-1) as OrderCode FROM 
(
    SELECT OrderUser,
    (
        SELECT OrderCode + ',' FROM tbOrder WHERE OrderUser=a.OrderUser FOR XML PATH('')
    ) AS b
    FROM tbOrder a GROUP BY OrderUser
) s

输出的结果如下

OrderUser  OrderCode

user1    201301030001,201301050001
user2    201301070001,201301030001
user3    201301040001

 

3. 参考网址 

http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html 

http://www.soft6.com/tech/16/161904.html