如何编写适配含表头关联子段XML的XSLT输出单表用于SSIS导入MS SQL

以下XSLT基于XSLT 1.0版本编写，适配SSIS内置XML组件的支持范围，可直接将你提供的嵌套XML结构转换为符合导入要求的二维行格式。

核心实现逻辑 #

• 全局预加载Header段的Question_map映射表，后续可以快速匹配每个变量对应的问题文本
• 以每个Patientleveldata节点为单位提取调研公共字段：survey_id、client_id、service、recdate、disdate
• 统一遍历所有分组（Analysis、Demographics、HCAHPS、Comments）下的回答节点，将公共字段与单条回答的变量名、答案、情绪值等字段拼接为单行数据
• 自动匹配Question_map补全对应变量的问题文本

完整XSLT代码 #

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <!-- 输出为CSV格式，也可修改method为xml适配SSIS XML行集输入 -->
    <xsl:output method="text" encoding="UTF-8" indent="no"/>
    <!-- 全局存储问题映射表 -->
    <xsl:variable name="questionMap" select="/Data_export/Header/Question_map/Question" />

    <xsl:template match="/">
        <!-- 输出表头行，不需要可以删除此行 -->
        <xsl:text>survey_id,client_id,service,recdate,disdate,varname,question_text,value,sentiment&#10;</xsl:text>
        <xsl:apply-templates select="/Data_export/Patientleveldata" />
    </xsl:template>

    <xsl:template match="Patientleveldata">
        <!-- 提取当前调研的公共字段 -->
        <xsl:variable name="commonFields">
            <xsl:value-of select="survey_id" /><xsl:text>,</xsl:text>
            <xsl:value-of select="client_id" /><xsl:text>,</xsl:text>
            <xsl:value-of select="service" /><xsl:text>,</xsl:text>
            <xsl:value-of select="recdate" /><xsl:text>,</xsl:text>
            <xsl:value-of select="disdate" /><xsl:text>,</xsl:text>
        </xsl:variable>

        <!-- 遍历当前调研下所有回答节点，兼容大小写不同的Response节点 -->
        <xsl:for-each select=".//response | .//Response">
            <xsl:variable name="currentVarname" select="Varname" />
            <!-- 匹配对应变量的问题文本 -->
            <xsl:variable name="currentQuestion" select="$questionMap[varname = $currentVarname]/question_text" />
            
            <!-- 输出单行数据 -->
            <xsl:copy-of select="$commonFields" />
            <xsl:value-of select="$currentVarname" /><xsl:text>,</xsl:text>
            <xsl:value-of select="$currentQuestion" /><xsl:text>,</xsl:text>
            <xsl:value-of select="translate(value, ',', '，')" /><xsl:text>,</xsl:text>
            <xsl:value-of select="sentiment" /><xsl:text>&#10;</xsl:text>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

SSIS使用步骤 #

1. 将上述代码保存为后缀为.xsl的文件
2. 打开SSIS的数据流任务，添加「XML源」组件
3. 在XML源编辑器中，选择你的XML数据文件路径，勾选「使用XSLT转换」，选择刚才保存的xsl文件
4. 输出列会自动识别，直接映射到SQL Server目标表的对应字段即可

注意事项 #

• 上述代码中对value字段的逗号做了中文转换处理，避免CSV格式错乱，如果输出格式为XML可以删除translate函数
• 请根据你实际XML的节点拼写调整匹配规则，比如你示例中Comments下的value节点有笔误为valeu，实际使用时请修正
• 如果需要过滤特定service的调研数据，可以在Patientleveldata的匹配规则中增加service匹配判断

内容的提问来源于stack exchange，提问作者Seth Calkins