代码之家 › 专栏 › 技术社区 › David Kroukamp

无法从命名空间中读取XML节点

namespaces tsql xml sql-server sql

David Kroukamp · 技术社区 · 8 年前

基本上我有一个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个以上的货币每立方的时间。

我如何迭代/选择它们?

1 回复 | 直到 8 年前

Andrea 8 年前

一旦填充了 @xml 变量,而不是使用 exec sp_xml_preparedocument ,请尝试使用 ;with xml namespaces来定义所需的命名空间 :。

我在XML的末尾添加了缺少的结束标记 </gesmes:envelope> ,因此我用以下XML测试了我的查询:


   
    <gesmes:envelope xmlns:gesmes=“http://www.gesmes.org/xml/2002-08-01”xmlns=“http://www.ecb.int/worsary/2002-08-01/eurofxref”>
<多维数据集>
<cube time=“2018-06-15”>
<cube currency=“美元”rate=“1.1596”/>
</cube>
</cube>
</gesmes:信封>
这是从XML变量@xml检索THA数据的查询




declare@xml xml='<gesmes:envelope xmlns:gesmes=“http://www.gesmes.org/xml/2002-08-01”xmlns=“http://www.ecb.int/worsary/2002-08-01/eurofxref”><cube><cube time=“2018-06-15”><cube currency=“usd”rate=“1.1596”/>/cube>/cube></gesmes:enveloPE>'

;使用xmlnamespaces(“http://www.gesmes.org/xml/2002-08-01”作为gesmes,
'http://www.ecb.int/worsary/2002-08-01/eurofxref'作为ns)
选择
t.x.value('ns:cube[1]/@currency','varchar(500)'作为[货币],
t.x.value('ns:cube[1]/@rate','varchar(500)'作为[速率],
t.x.value('./@时间','varchar(500)'作为[时间]
从
@xml.nodes('/gesmes:envelope/ns:cube/ns:cube')作为t(x)
结果:



要处理多个cube>tags:。


<gesmes:envelope xmlns:gesmes=“http://www.gesmes.org/xml/2002-08-01”xmlns=“http://www.ecb.int/worsary/2002-08-01/eurofxref”>
<多维数据集>
<cube time=“2018-06-15”>
<cube currency=“美元”rate=“1.2345”/>
<cube currency=“zar”rate=“10.1”/>
</cube>
<cube time=“2018-06-16”>
<cube currency=“美元”rate=“1.1596”/>
<cube currency=“zar”rate=“9.546”/>
</cube>
</cube>
</gesmes:信封>
您可以使用此查询:

declare@xml xml='<gesmes:envelope xmlns:gesmes=“http://www.gesmes.org/xml/2002-08-01”xmlns=“http://www.ecb.int/woretary/2002-08-01/eurofxref”><cube><cube time=“2018-06-15”><cube currency=“usd”rate=“1.2345”/>gt;<cube currency=“zar”rate=“10.1”/>gt;/lt;/cube><cube time=“2018-06-16”><cube currency=“USD”rate=“1.1596”/>gt;<cube currency=“zar”rate=“9.546”/>gt;/cube></cube></gesmes:envelope>'

;使用xmlnamespaces(“http://www.gesmes.org/xml/2002-08-01”作为gesmes,
'http://www.ecb.int/worsary/2002-08-01/eurofxref'作为ns)
选择t.x.value('(.)[1]/@currency','varchar(500)'作为[货币]
,t.x.value('(.)[1]/@rate','varchar(500)'作为[速率]
,t.x.value('(..)[1]/@time','varchar(500)'作为[时间]
从@xml.nodes('/gesmes:envelope/ns:cube/ns:cube/ns:cube')起,作为t(x)
结果:


使用;WITH XMLNAMESPACES以下内容:

我加了缺失的结束标签</gesmes:Envelope>在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>


这是从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)


结果: