使用XSLT从XML生成指定格式CSV失败，寻求解决方法

问题描述 #

无法使用XSLT正确处理XML文件生成符合要求的汇率CSV，目标CSV需要包含固定字段和从XML提取的汇率数据。

目标CSV格式示例 #

CalculateInverseRate,CalculateCrossRates,CrossRatesAnchorCurrency,EffectiveTimestamp,FromCurrency,TargetCurrency,CurrencyRateType,CurrencyRate
0,1,EUR,2023-11-22,EUR,USD,Current,1.0928
0,1,EUR,2023-11-22,EUR,JPY,Current,162.12
...

源XML文件 #

<?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    <gesmes:subject>Reference rates</gesmes:subject>
    <gesmes:Sender>
        <gesmes:name>European Central Bank</gesmes:name>
    </gesmes:Sender>
    <Cube>
        <Cube time='2023-11-22'>
            <Cube currency='USD' rate='1.0928'/>
            <Cube currency='JPY' rate='162.12'/>
            <Cube currency='BGN' rate='1.9558'/>
            <Cube currency='CZK' rate='24.541'/>
            <Cube currency='DKK' rate='7.4565'/>
            <Cube currency='GBP' rate='0.87630'/>
            <Cube currency='HUF' rate='378.90'/>
            <Cube currency='PLN' rate='4.3690'/>
            <Cube currency='RON' rate='4.9721'/>
            <Cube currency='SEK' rate='11.4270'/>
            <Cube currency='CHF' rate='0.9665'/>
            <Cube currency='ISK' rate='152.50'/>
            <Cube currency='NOK' rate='11.7200'/>
            <Cube currency='TRY' rate='31.4332'/>
            <Cube currency='AUD' rate='1.6669'/>
            <Cube currency='BRL' rate='5.3364'/>
            <Cube currency='CAD' rate='1.4994'/>
            <Cube currency='CNY' rate='7.8378'/>
            <Cube currency='HKD' rate='8.5165'/>
            <Cube currency='IDR' rate='16881.57'/>
            <Cube currency='ILS' rate='4.0811'/>
            <Cube currency='INR' rate='91.1060'/>
            <Cube currency='KRW' rate='1412.56'/>
            <Cube currency='MXN' rate='18.7590'/>
            <Cube currency='MYR' rate='5.1006'/>
            <Cube currency='NZD' rate='1.8132'/>
            <Cube currency='PHP' rate='60.497'/>
            <Cube currency='SGD' rate='1.4641'/>
            <Cube currency='THB' rate='38.494'/>
            <Cube currency='ZAR' rate='20.1445'/>
        </Cube>
    </Cube>
</gesmes:Envelope>

原XSLT代码 #

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" />

    <!-- Static values -->
    <xsl:variable name="CalculateInverseRate" select="0"/>
    <xsl:variable name="CalculateCrossRates" select="1"/>
    <xsl:variable name="CrossRatesAnchorCurrency" select="'EUR'"/>
    <xsl:variable name="FromCurrency" select="'EUR'"/>
    <xsl:variable name="CurrencyRateType" select="'Current'"/>
    <xsl:variable name="EffectiveTimestamp" select="/gesmes:Envelope/Cube/Cube/@time"/>
    <xsl:variable name="newline">
        <xsl:text>
        </xsl:text>
    </xsl:variable>

    <xsl:template match="/">
        <xsl:value-of
            select="concat('CalculateInverseRate,CalculateCrossRates,CrossRatesAnchorCurrency,EffectiveTimestamp,FromCurrency,TargetCurrency,CurrencyRateType,CurrencyRate',$newline)" />
        <xsl:for-each select="//Cube">
            <xsl:for-each select="/Cube">
                <xsl:variable name="TargetCurrency" select="@currency"/>
                <xsl:variable name="CurrencyRate" select="@rate"/>
                <xsl:value-of
                    select="concat($CalculateInverseRate,',',$CalculateCrossRates,',',$CrossRatesAnchorCurrency,',',$EffectiveTimestamp,',',$FromCurrency,',',$TargetCurrency,',',$CurrencyRateType,',',$CurrencyRate,',',$newline)"/>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

问题分析 #

1. 未处理默认命名空间：XML中定义了默认命名空间http://www.ecb.int/vocabulary/2002-08-01/eurofxref，原XSLT未声明该命名空间，导致XPath无法匹配任何Cube元素。
2. 循环逻辑错误：原代码嵌套//Cube和/Cube循环，/Cube是根路径选择，完全无法定位到目标汇率节点。
3. 时间戳获取失败：由于命名空间问题，EffectiveTimestamp的XPath无法正确获取父节点的time属性。
4. 多余逗号和换行空格：原concat末尾多了一个逗号，换行符包含多余空格，会导致CSV格式混乱。

修改后的XSLT代码 #

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" 
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:ecb="http://www.ecb.int/vocabulary/2002-08-01/eurofxref"
                xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01"
                exclude-result-prefixes="ecb gesmes">
    <xsl:output method="text" encoding="UTF-8" />

    <!-- 静态变量定义 -->
    <xsl:variable name="CalculateInverseRate" select="0"/>
    <xsl:variable name="CalculateCrossRates" select="1"/>
    <xsl:variable name="CrossRatesAnchorCurrency" select="'EUR'"/>
    <xsl:variable name="FromCurrency" select="'EUR'"/>
    <xsl:variable name="CurrencyRateType" select="'Current'"/>
    <!-- 定义标准换行符 -->
    <xsl:variable name="newline" select="'&#10;'" />

    <xsl:template match="/">
        <!-- 输出CSV表头 -->
        <xsl:text>CalculateInverseRate,CalculateCrossRates,CrossRatesAnchorCurrency,EffectiveTimestamp,FromCurrency,TargetCurrency,CurrencyRateType,CurrencyRate</xsl:text>
        <xsl:value-of select="$newline" />
        
        <!-- 遍历所有汇率节点 -->
        <xsl:for-each select="gesmes:Envelope/ecb:Cube/ecb:Cube/ecb:Cube">
            <!-- 获取当前汇率的时间戳（父节点的time属性） -->
            <xsl:variable name="EffectiveTimestamp" select="../@time" />
            <xsl:variable name="TargetCurrency" select="@currency"/>
            <xsl:variable name="CurrencyRate" select="@rate"/>
            
            <!-- 输出CSV行 -->
            <xsl:value-of select="concat(
                $CalculateInverseRate, ',',
                $CalculateCrossRates, ',',
                $CrossRatesAnchorCurrency, ',',
                $EffectiveTimestamp, ',',
                $FromCurrency, ',',
                $TargetCurrency, ',',
                $CurrencyRateType, ',',
                $CurrencyRate,
                $newline
            )" />
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

修改说明 #

1. 声明命名空间：添加默认命名空间绑定（前缀ecb）和gesmes命名空间，确保XPath能正确匹配XML元素。
2. 修正循环逻辑：直接通过完整路径gesmes:Envelope/ecb:Cube/ecb:Cube/ecb:Cube定位到最内层的汇率节点，无需嵌套循环。
3. 正确获取时间戳：通过../@time获取父节点的时间戳，确保每个汇率行都能正确关联日期。
4. 清理格式问题：使用标准换行符
，移除原代码中多余的逗号和空格，保证CSV格式规范。
5. 排除冗余前缀：使用exclude-result-prefixes避免命名空间前缀出现在输出中。

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