TSQL拆分XML节点

Question

我如何可以查询表，看起来像这样：

DECLARE @test as table (
    [Id] int, 
    [Changes] XML 
    ) 

insert into @test VALUES (1,'<insert pk="1"/><insert pk="2"/><delete pk="1"/><delete pk="2"/>') 
insert into @test VALUES (2,'<insert pk="3"/>') 

为了得到这个输出？

1, <insert pk="1"/> 
1, <insert pk="2"/> 
1, <delete pk="1"/> 
1, <delete pk="2"/> 
2, <insert pk="3"/> 

Answer 1

尝试这样：

SELECT 
    ID, 
    XmlChanges.query('.') 
FROM 
    @test 
CROSS APPLY 
    Changes.nodes('/*') AS Nodes(XmlChanges) 

输出结果将是：

ID (No column name) 
1 <insert pk="1" /> 
1 <insert pk="2" /> 
1 <delete pk="1" /> 
1 <delete pk="2" /> 
2 <insert pk="3" />