sql - 无法从命名空间读取 xml 节点

Question

基本上我有一个类似于这样的 XML 文件：

<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    <Cube>
        <Cube time="2018-06-15">
            <Cube currency="USD" rate="1.2345"/>
            <Cube currency="ZAR" rate="10.1"/>
        </Cube>
        <Cube time="2018-06-16">
            <Cube currency="USD" rate="1.1596"/>
            <Cube currency="ZAR" rate="9.546"/>
        </Cube>
    </Cube>
</gesmes:Envelope>

我像这样插入它：

INSERT INTO 
    fxRatesXml(xmlData, updatedOn)
SELECT 
    CONVERT(XML, bulkColumn), 
    GETDATE() 
FROM 
    OPENROWSET(BULK 'I:\Downloads\eurofxref-hist-90d.xml', SINGLE_BLOB) as fxRateXmlData;

然后我尝试读取时间、货币和汇率，如下所示：

DECLARE @xml AS XML, @hDoc AS INT, @sql NVARCHAR (MAX)

SELECT 
    @xml = xmlData
FROM 
    fxRatesXml

EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml, '<root xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref"/>'

SELECT 
    *
FROM 
    OPENXML(@hDoc, 'gesmes:Cube')
WITH 
( 
    [time] nvarchar(max) 'Cube/time',
    currency nvarchar(max) 'Cube/Cube/currency',
    rate nvarchar(max) 'Cube/Cube/rate'
)


EXEC sp_xml_removedocument @hDoc

但没有运气。

我不确定我哪里出错了，任何帮助将不胜感激。

更新

将 XML 更新为更具体。它可以包括超过 2 个时间立方和超过 2 个货币/立方时间。

我将如何迭代/选择它们？

标签： sqlsql-serverxmltsqlnamespaces

Answer 1

一旦你填充了@xml变量，而不是 using EXEC sp_xml_preparedocument，尝试定义你需要的命名空间 using ;WITH XMLNAMESPACES：

我在 xml 末尾添加了缺少</gesmes:Envelope>的结束标记，因此我使用以下 xml 测试了我的查询：

<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    <Cube>
        <Cube time="2018-06-15">
            <Cube currency="USD" rate="1.1596"/>
        </Cube>
    </Cube>
</gesmes:Envelope>

这是从 xml 变量 @ 检索 tha 数据的查询xml：

declare @xml xml = '<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref"> <Cube> <Cube time="2018-06-15"> <Cube currency="USD" rate="1.1596"/> </Cube> </Cube> </gesmes:Envelope>'

;WITH XMLNAMESPACES('http://www.gesmes.org/xml/2002-08-01' AS gesmes,
                    'http://www.ecb.int/vocabulary/2002-08-01/eurofxref' as ns)
SELECT 
    T.X.value('ns:Cube[1]/@currency','varchar(500)') AS [currency],
    T.X.value('ns:Cube[1]/@rate','varchar(500)') AS [rate],
    T.X.value('./@time','varchar(500)') AS [time]
FROM 
    @xml.nodes('/gesmes:Envelope/ns:Cube/ns:Cube') AS T(X)

结果：

处理多个<Cube>标签：

<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    <Cube>
        <Cube time="2018-06-15">
            <Cube currency="USD" rate="1.2345"/>
            <Cube currency="ZAR" rate="10.1"/>
        </Cube>
        <Cube time="2018-06-16">
            <Cube currency="USD" rate="1.1596"/>
            <Cube currency="ZAR" rate="9.546"/>
        </Cube>
    </Cube>
</gesmes:Envelope>

你可以使用这个查询：

declare @xml xml ='<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref"> <Cube> <Cube time="2018-06-15"> <Cube currency="USD" rate="1.2345"/> <Cube currency="ZAR" rate="10.1"/> </Cube> <Cube time="2018-06-16"> <Cube currency="USD" rate="1.1596"/> <Cube currency="ZAR" rate="9.546"/> </Cube> </Cube> </gesmes:Envelope>'    

;WITH XMLNAMESPACES('http://www.gesmes.org/xml/2002-08-01' AS gesmes,
    'http://www.ecb.int/vocabulary/2002-08-01/eurofxref' as ns)
SELECT T.X.value('(.)[1]/@currency','varchar(500)') AS [currency]
     ,T.X.value('(.)[1]/@rate','varchar(500)') AS [rate]
     ,T.X.value('(..)[1]/@time','varchar(500)') AS [time]
FROM @xml.nodes('/gesmes:Envelope/ns:Cube/ns:Cube/ns:Cube') AS T(X)

结果：