如何获取指定链接XML汇率数据并导入SQL Server表的currency与rate列

第一步：创建目标存储表 #

首先在SQL Server中创建用于存储汇率数据的表，建议保留汇率对应的日期字段，方便后续历史数据查询：

CREATE TABLE EuroExchangeRate (
    rate_date DATE NOT NULL,
    currency CHAR(3) NOT NULL,
    rate DECIMAL(18,6) NOT NULL,
    update_time DATETIME DEFAULT GETDATE(),
    PRIMARY KEY (rate_date, currency)
)

第二步：数据提取导入（两种常用方案） #

方案1：纯T-SQL直接解析导入 #

该方案无需额外工具，直接在SQL Server中通过XML解析能力完成数据抽取，适合临时同步或轻量级定时任务场景：

DECLARE @xml XML
-- 此处可替换为实际拉取到的XML内容，也可通过OLE Automation请求远程地址获取
SET @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="2021-11-12">
            <Cube currency="USD" rate="1.1448"/>
            <Cube currency="JPY" rate="130.50"/>
            <!-- 此处省略其余汇率节点 -->
        </Cube>
    </Cube>
</gesmes:Envelope>'

-- 解析XML并插入数据，自动跳过已存在的重复数据
WITH XMLNAMESPACES (
    'http://www.gesmes.org/xml/2002-08-01' AS gesmes,
    DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref'
)
INSERT INTO EuroExchangeRate (rate_date, currency, rate)
SELECT 
    T.c.value('../@time', 'DATE') AS rate_date,
    T.c.value('@currency', 'CHAR(3)') AS currency,
    T.c.value('@rate', 'DECIMAL(18,6)') AS rate
FROM @xml.nodes('/Envelope/Cube/Cube/Cube') AS T(c)
WHERE NOT EXISTS (
    SELECT 1 FROM EuroExchangeRate e 
    WHERE e.rate_date = T.c.value('../@time', 'DATE') 
    AND e.currency = T.c.value('@currency', 'CHAR(3)')
)

如果需要自动拉取远程XML内容，可开启OLE Automation Procedures后通过HTTP请求获取远程XML内容，注意提前评估服务器安全权限。

方案2：SSIS可视化同步（适合定时自动同步场景） #

如果需要每日自动同步最新汇率，可通过SSIS包实现无代码化配置：

• 新建SSIS包，添加HTTP连接管理器，配置地址为欧洲央行汇率XML地址
• 添加XML数据源，指定使用上述HTTP连接读取XML内容，自动生成XSD结构后选择最内层Cube节点作为输出
• 添加OLE DB目标组件，连接到SQL Server的EuroExchangeRate表，映射time到rate_date、currency到currency、rate到rate字段
• 部署SSIS包后通过SQL Server代理作业配置每日定时执行即可

注意事项 #

• 欧洲央行汇率每个工作日更新1次，定时同步频率设置为每日1次即可
• 所有汇率均为1欧元兑换对应货币的比值，如需计算其他货币兑欧元的汇率取对应rate的倒数即可

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