sql - SQL Server 表到嵌套的 xml 文件

Question

我有一张这样的桌子：

Action Action2  Name    Action3 Batch
--------------------------------------
add      PL    Steve    add      1
add      PL    Steve    add      3
add      PL    Steve    add      4
add      PL    Steve    add      5
add      PL    Steve    add      1
add      PL    Steve    add      3
add      PL    Steve    add      4
add      PL    Steve    add      5

并需要将其转换为这样的 XML 文档：

标签： sqlsql-servertsqlxquery

Answer 1

感谢@Isaac 的数据脚本！

for <List> elements in <Branch> node, like <Branch><List/><List/></Branch>:

CREATE TABLE #mytable
(
   Action  VARCHAR(10),
   Action2 VARCHAR(10),
   Name    VARCHAR(50),
   Action3 VARCHAR(10),
   Batch   INT
);

INSERT INTO #mytable(Action,Action2,Name,Action3,Batch) 
VALUES 
('add','PL','Steve','add',1),
('add','PL','Steve','add',3),
('add','PL','Steve','add',4),
('add','PL','Steve','add',5),
('add','PL','Steve','add',1),
('add','PL','Steve','add',3),
('add','PL','Steve','add',4),
('add','PL','Steve','add',5);


INSERT INTO #mytable(Action,Action2,Name,Action3,Batch) 
VALUES 
('update','PL','John','insert',5),
('update','PL','Paul','insert',1),
('update','PL','Chris','delete',3),
('update','PL','Mary','update',4),
('update','PL','Jane','delete',5);


select a1.Action as '@Action', s.brancexml as '*'
from
(
select distinct Action
from #mytable
) as a1
cross apply 
(
    select
    (
        select a2.Action2 AS '@Action', a2.Name as '@Name', x.listxml as     '*'
        from
    (
        select distinct Action2, Name
        from #mytable AS b
        where b.Action = a1.Action
    ) AS a2
    cross apply
    (
        select
        (
            select distinct c.Action3 as '@Action', c.Batch as '@Batch'
            from #mytable AS c
            where c.Action = a1.Action AND c.Action2 = a2.Action2 AND c.Name = a2.Name
            for xml path('List'), type
        ) AS listxml
    ) as x
    for xml path('Brance'), type
    ) as brancexml
) as s
for xml path('Start'), root('Entries'), type