XML数据整合需求：求助编写XSL样式表合并Policy节点

我有个项目需要XML数据馈送，但我不是XML开发人员。我写了SQL语句生成了下面的XML结果，但需要调整格式：把相同PolicyNumber和TransactionID的Policy节点合并，将它们的Coverage子节点放到同一个Coverages节点下。

当前生成的XML结果 #

<Batch>
  <Policy>
    <PolicyNumber>2134</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1234">50.00</Coverage>
    </Coverages>
  </Policy>
  <Policy>
    <PolicyNumber>2134</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1235">30.00</Coverage>
    </Coverages>
  </Policy>
  <Policy>
    <PolicyNumber>3124</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1234">10.00</Coverage>
    </Coverages>
  </Policy>
</Batch>

期望的XML格式 #

<Batch>
  <Policy>
    <PolicyNumber>2134</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1234">50.00</Coverage>
      <Coverage CvgCode="1235">30.00</Coverage>
    </Coverages>
  </Policy>
  <Policy>
    <PolicyNumber>3124</PolicyNumber>
    <TransactionID>1</TransactionID>
    <Coverages>
      <Coverage CvgCode="1234">10.00</Coverage>
    </Coverages>
  </Policy>
</Batch>

生成数据的SQL代码 #

CREATE Table ReportData (
    PolicyNumber varchar(20) null
    ,TransactionID int null
    ,ClassCode varchar(5) null
    ,Amount money null
)

INSERT INTO ReportData
VALUES
('2134',1,'1234',50.00)
,('2134',1,'1235',30.00)
,('3124',1,'1234',10.00)

SELECT 
    [PolicyNumber] 
    ,TransactionID
    ,ClassCode "Coverages/Coverage/@CvgCode"
    ,Amount "Coverages/Coverage"
FROM ReportData 
FOR XML PATH ('Policy'), ROOT('Batch'), ELEMENTS

解决方案 #

方案1：修改SQL直接生成目标XML #

原SQL每行数据生成一个Policy节点，导致重复。可以通过分组嵌套的方式，直接生成聚合后的XML：

SELECT 
    rd.PolicyNumber,
    rd.TransactionID,
    (
        SELECT 
            ClassCode AS "@CvgCode",
            Amount AS "."
        FROM ReportData sub_rd
        WHERE sub_rd.PolicyNumber = rd.PolicyNumber 
          AND sub_rd.TransactionID = rd.TransactionID
        FOR XML PATH('Coverage'), ROOT('Coverages'), TYPE
    )
FROM ReportData rd
GROUP BY rd.PolicyNumber, rd.TransactionID
FOR XML PATH('Policy'), ROOT('Batch'), ELEMENTS

说明：

• 外层查询按PolicyNumber和TransactionID分组，每个分组生成一个Policy节点
• 子查询针对当前分组的所有数据，生成多个Coverage节点并包裹在Coverages下，通过TYPE保留XML结构

方案2：使用XSLT合并节点 #

如果必须处理已生成的XML，可以用XSLT的Muenchian分组实现合并：

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:key name="policyGroup" match="Policy" use="concat(PolicyNumber, '|', TransactionID)" />

    <xsl:template match="/Batch">
        <Batch>
            <!-- 遍历每个唯一的Policy分组 -->
            <xsl:for-each select="Policy[generate-id() = generate-id(key('policyGroup', concat(PolicyNumber, '|', TransactionID))[1])]">
                <Policy>
                    <PolicyNumber><xsl:value-of select="PolicyNumber"/></PolicyNumber>
                    <TransactionID><xsl:value-of select="TransactionID"/></TransactionID>
                    <Coverages>
                        <!-- 收集当前分组下所有Coverage节点 -->
                        <xsl:for-each select="key('policyGroup', concat(PolicyNumber, '|', TransactionID))">
                            <xsl:copy-of select="Coverages/Coverage"/>
                        </xsl:for-each>
                    </Coverages>
                </Policy>
            </xsl:for-each>
        </Batch>
    </xsl:template>
</xsl:stylesheet>

说明：

• 用xsl:key定义分组键，基于PolicyNumber和TransactionID的组合
• 外层循环遍历每个分组的第一个Policy节点，生成合并后的Policy结构
• 内层循环收集该分组下所有Policy节点中的Coverage子节点，放到同一个Coverages下

内容的提问来源于stack exchange，提问作者Landy Johnson