如何为SQL Server XML节点生成关联HeadId，实现数据拆分？

我们有如下MS SQL Server格式的XML数据：

<Features>
  <Item>
   <Description>First Descr.</Description>
   <Comment>First comment</Comment>
   <C0001>12</C0001>
   <C0002>23</C0002>
  </Item>   
  <Item>
   <Description>Second Descr.</Description>
   <Comment>Second comment</Comment>
   <C0001>212</C0001>
   <C0002>223</C0002>
   <C0003>323</C0003>
  </Item>   
</Features>

需要将其拆分存储到Head和Items两个独立表中，期望结果如下：

Head表：

HeadID |  Description   | Comment 
1      |  First Descr.  | First comment
2      |  Second Descr. | Second comment

Items表：

HeadID    | Name  | ResValue
1         | C0001 | 12
1         | C0002 | 23
2         | C0001 | 212
2         | C0002 | 223
2         | C0003 | 323

目前用OPENXML结合ROW_NUMBER()生成Head表的HeadID是正确的，但处理Items表时，现有代码生成的HeadID是连续递增的1、2、3、4、5，无法和原Item节点对应。现有Items表处理代码：

SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as HeadId,
    unpvt.[Name],
    TRY_PARSE(REPLACE(unpvt.ResValue,',','.') AS DECIMAL(12, 6)) as ResValue
    FROM OPENXML (@idoc, '/Features/Item', 1)
        WITH (
            Description nvarchar(255) 'Description',
            Comment nvarchar(4000) 'Comment',
            C0001 nvarchar(20) 'C0001', 
            C0002 nvarchar(20) 'C0002', 
            C0003 nvarchar(20) 'C0003'
        ) x
        UNPIVOT ([ResValue] FOR [Name] IN (C0001,C0002,C0003)) AS unpvt
    WHERE TRY_PARSE(REPLACE(unpvt.ResValue,',','.') AS DECIMAL(12, 6)) <> 0

解决方案 #

问题出在你把ROW_NUMBER()放在了UNPIVOT之后，这时候它会对所有展开后的行（共5行）生成连续ID，而不是按原Item节点分组。正确的做法是先在OPENXML返回的每个Item节点层面生成对应的HeadID，再执行UNPIVOT，这样每个展开后的行都会继承所属Item的HeadID。

修改后的核心代码

SELECT 
    x.HeadId,
    unpvt.[Name],
    TRY_PARSE(REPLACE(unpvt.ResValue,',','.') AS DECIMAL(12, 6)) as ResValue
FROM (
    -- 先为每个Item节点生成对应的HeadID
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as HeadId,
        C0001, C0002, C0003
    FROM OPENXML (@idoc, '/Features/Item', 1)
        WITH (
            C0001 nvarchar(20) 'C0001', 
            C0002 nvarchar(20) 'C0002', 
            C0003 nvarchar(20) 'C0003'
        )
) x
UNPIVOT ([ResValue] FOR [Name] IN (C0001,C0002,C0003)) AS unpvt
WHERE TRY_PARSE(REPLACE(unpvt.ResValue,',','.') AS DECIMAL(12, 6)) <> 0

可选：关联Head表确保ID一致

如果已经生成了Head表，也可以通过Description和Comment关联获取HeadID，彻底保证两个表的ID完全匹配：

SELECT 
    h.HeadId,
    unpvt.[Name],
    TRY_PARSE(REPLACE(unpvt.ResValue,',','.') AS DECIMAL(12, 6)) as ResValue
FROM OPENXML (@idoc, '/Features/Item', 1)
    WITH (
        Description nvarchar(255) 'Description',
        Comment nvarchar(4000) 'Comment',
        C0001 nvarchar(20) 'C0001', 
        C0002 nvarchar(20) 'C0002', 
        C0003 nvarchar(20) 'C0003'
    ) x
UNPIVOT ([ResValue] FOR [Name] IN (C0001,C0002,C0003)) AS unpvt
JOIN Head h ON h.Description = x.Description AND h.Comment = x.Comment
WHERE TRY_PARSE(REPLACE(unpvt.ResValue,',','.') AS DECIMAL(12, 6)) <> 0

内容的提问来源于stack exchange，提问作者Zoltan Hernyak