时间戳

首页 > 解决方案 > 从 Web 链接获取数据 XML 文件并显示在 SQL Server 表上

sql - 从 Web 链接获取数据 XML 文件并显示在 SQL Server 表上

问题描述

我有这个链接https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml我想在 SQL Server 表中放入货币和汇率等两列，如您在链接上看到的那样。

我怎样才能做到这一点？

我会感谢你的帮助。

<?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 currency='BGN' rate='1.9558'/>
            <Cube currency='CZK' rate='25.238'/>
            <Cube currency='DKK' rate='7.4370'/>
            <Cube currency='GBP' rate='0.85505'/>
            <Cube currency='HUF' rate='366.15'/>
            <Cube currency='PLN' rate='4.6428'/>
            <Cube currency='RON' rate='4.9488'/>
            <Cube currency='SEK' rate='10.0085'/>
            <Cube currency='CHF' rate='1.0568'/>
            <Cube currency='ISK' rate='150.40'/>
            <Cube currency='NOK' rate='9.9508'/>
            <Cube currency='HRK' rate='7.5123'/>
            <Cube currency='RUB' rate='82.6649'/>
            <Cube currency='TRY' rate='11.3987'/>
            <Cube currency='AUD' rate='1.5690'/>
            <Cube currency='BRL' rate='6.1902'/>
            <Cube currency='CAD' rate='1.4416'/>
            <Cube currency='CNY' rate='7.3047'/>
            <Cube currency='HKD' rate='8.9206'/>
            <Cube currency='IDR' rate='16239.91'/>
            <Cube currency='ILS' rate='3.5600'/>
            <Cube currency='INR' rate='85.1930'/>
            <Cube currency='KRW' rate='1349.10'/>
            <Cube currency='MXN' rate='23.6472'/>
            <Cube currency='MYR' rate='4.7692'/>
            <Cube currency='NZD' rate='1.6293'/>
            <Cube currency='PHP' rate='56.995'/>
            <Cube currency='SGD' rate='1.5494'/>
            <Cube currency='THB' rate='37.527'/>
            <Cube currency='ZAR' rate='17.4919'/>
        </Cube>
    </Cube>
</gesmes:Envelope>

标签： sqlsql-serverxml

解决方案

看起来你需要这样的东西：

WITH
  DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref',
  'http://www.gesmes.org/xml/2002-08-01' AS gesmes)
INSERT YourRateTable (Currency, Rate)
SELECT
  x.cube.value('@currency','char(3)'),
  x.cube.value('@rate','decimal(18,9)')
FROM @xml.nodes('/gesmes:Envelope/Cube/Cube/Cube') x(cube);

db<>小提琴

要将数据导入 SQL Server，请不要尝试使用诸如sp_OA加载数据之类的技巧。

相反，使用类似 Powershell 的东西Invoke-WebRequest，并将其传递给 usingInvoke-SqlCmd或 dba-tools'sInvoke-Query

Install-Module dbatools

$xml = (Invoke-WebRequest -Uri "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml").Content;

$query = @"THEABOVEQUERY";

Invoke-DbaQuery -SqlInstance "yourserver" -Query $query -SqlParameter @{ Name = "@xml"; Value = $xml; SqlDbType = "Xml" };

推荐阅读