在 SQL 查询中分析 XML 元素的属性

Question

需要在某些工具中存储ECB汇率，MS SQL server查询存储在该工具中，并且无法从XML网页获取信息。

这是带有汇率的web上的源XMLhttps://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

这些是我的实验：我创建XML变量并用XML填充(稍后XML将在表的nvarchar列中(

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>';

我试过这些，没人管用。

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

有什么不同的简单方法吗？我想选择一个有两列的临时表，currenty和rate。谢谢你的尝试。。。

Answer 1

以下是如何正确操作。

这两个名称空间都应该小心。正如@RonenAriely所提到的，您需要声明和使用命名空间。

以后的XML将在表的nvarchar列中

最好使用XML数据类型的列。

优点：

• XML数据类型存储远小于NVARCHAR(MAX)
• XML数据类型具有强大的XQueryneneneba API来处理XML的任何操作数据
• XML数据类型具有特殊的XML索引以提高性能。3种类型的索引

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);

输出

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