Parse XML element's attributes in SQL query-CodePudding

Need to store ECB exchange rates in some tool, where MS SQL server queries are stored, and cannot get info from XML web page.

This is source XML on web with exchange rates https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

These are my experiments: I create XML variable and fill by XML (later XML will be in nvarchar column in a table)

DECLARE @RateXML XML
SELECT @RateXML = '
<?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="2022-02-16">
            <Cube currency="USD" rate="1.1372"/>
            <Cube currency="JPY" rate="131.56"/>
            <Cube currency="BGN" rate="1.9558"/>
        </Cube>
    </Cube>
</gesmes:Envelope>';

I tried these, no one works.

WITH XMLNAMESPACES ('uri' as gesmes)
select a.currency.query('Cube/@currency') as currency from @RateXML.nodes('gesmes:Envelope/Cube/Cube') as a(currency);
--Error: Attribute may not appear outside of an element
    
WITH XMLNAMESPACES ('uri' as gesmes)
select a.currency.value('Cube/@currency', 'varchar(max)') as currency from @RateXML.nodes('gesmes:Envelope/Cube/Cube') as a(currency);
-- 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
    
WITH XMLNAMESPACES ('uri' as gesmes)
select @RateXML.query('gesmes:Envelope/Cube/Cube/Cube/@currency') as currency;
-- Attribute may not appear outside of an element

Is there any different simple approach? I would like to select a temporary table with 2 columns, currenty and rate. Thanks for trying ...

CodePudding user response：

Here is how to do correctly.

Both namespaces should be taken care. As @RonenAriely mentioned, you need to declare and use namespaces.

later XML will be in nvarchar column in a table

It is better to use column of XML data type for that.

Benefits:

XML data type storage is much less than NVARCHAR(MAX)
XML data type has powerful XQuery API to handle any operation for the XML data.
XML data type has special XML indexes for performance. 3 types of indexes.

SQL

DECLARE @RateXML 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="2022-02-16">
            <Cube currency="USD" rate="1.1372"/>
            <Cube currency="JPY" rate="131.56"/>
            <Cube currency="BGN" rate="1.9558"/>
        </Cube>
    </Cube>
</gesmes:Envelope>';

;WITH XMLNAMESPACES (DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref'
    , 'http://www.gesmes.org/xml/2002-08-01' AS gesmes)
SELECT c.value('@currency', 'CHAR(3)') AS currency
    , c.value('@rate', 'DECIMAL(10,4)') AS rate
FROM @RateXML.nodes('/gesmes:Envelope/Cube/Cube/Cube') AS t(c);

Output

 ---------- ---------- 
| currency |   rate   |
 ---------- ---------- 
| USD      |   1.1372 |
| JPY      | 131.5600 |
| BGN      |   1.9558 |
 ---------- ----------