Get data XML file from web link and show on SQL Server table

Question

I have this link https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml and I want to put in a SQL Server table two columns like currency and rate as you can see on link.

How can I do that?

I will appreciate your help.

<?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>

A question here is supposed to be self-contained. I.e. external links aren't enough, show us some sample table data and the expected result as formatted text. Simplify if possible, i.e. [mcve]. — jarlh, Nov 15 '21 at 09:59
Without the link (which as explained, shouldn't be a link, but should be content *in*) your question, your question effectively becomes *"I want to put in SqlTable two COLUMNS like currency and rate. How can I do that?"* Which is very unclear. — Thom A, Nov 15 '21 at 10:10

Charlieface · Accepted Answer · 2021-11-16T12:57:31.653

2

It looks like you need something like this:

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<>fiddle

To get your data into SQL Server, do not be tempted to use hacks such as sp_OA to load it in.

Instead, use something like Powershell Invoke-WebRequest, and pass it in using Invoke-SqlCmd or dba-tools's Invoke-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" };

edited Nov 16 '21 at 12:57

answered Nov 15 '21 at 12:32

Charlieface

52,284
6
19
43

This is exactly what I asked so thank you, but how can I get these XML values directly from web, without putting XML in Sql Server. I hope I was clear, Thank you in advance, – Ingis Nov 15 '21 at 15:08
You can use Powershell or similar for that – Charlieface Nov 15 '21 at 15:10
OK this is how I solve the problem – Ingis Nov 16 '21 at 09:39
In this link you will find the suolucion **[Solucion](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8a4714b5c2317546c5a182f6df58fd8e) ** – Ingis Nov 16 '21 at 12:33
I said **"do not be tempted to use hacks such as sp_OA"** it's a ***really*** bad idea. See also https://dba.stackexchange.com/a/158514/220697 and https://stackoverflow.com/a/8328549/14868997 – Charlieface Nov 16 '21 at 12:43
But I cannot do with Invoke-WebRequest, How can I do that, can you be more specific pelase – Ingis Nov 16 '21 at 13:13
What about the code I added doesn't work, why doesn't `Invoke-WebRequest` work? Open Powershell and paste the code in. Where I put `THEABOVEQUERY` you paste the SQL in. Where I put `yourserver` you put your server details – Charlieface Nov 16 '21 at 13:37
Sorry I don't get you, Why I must do it from PowerShell, instead from SQL Server? – Ingis Nov 16 '21 at 14:26
Yes, as I said multiple times: dowloading files from TSQL is a bad idea. TSQL us not a general scripting language, it's meant for database queries. Use a proper scripting language instead, such as Powershell, Python or C#. – Charlieface Nov 16 '21 at 14:27

Get data XML file from web link and show on SQL Server table

1 Answers1