I've been looking at the examples here but I'm still struggling to query my XML properly. I've been trying to break down my XML via SQL xml.nodes query but I don't get any results
This is the XML sample:
<?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="2013-12-09">
<Cube currency="USD" rate="1.3722"/>
<Cube currency="EUR" rate="1.0"/>
</Cube>
<Cube time="2013-12-10">
<Cube currency="USD" rate="1.5555"/>
<Cube currency="EUR" rate="1.2"/>
</Cube>
</Cube>
</gesmes:Envelope>
I need to insert these values into a table in the following format:
MyDate Currency Rate
2013-12-09 USD 1.3722
2013-12-09 EUR 1.0
2013-12-10 USD 1.5555
2013-12-10 EUR 1.2
After a lot of attempts, I thought I'd try removing the gesmes* nodes and just replace it with and I actually managed to get the values.
Here's my SQL which worked with :
declare @xml as xml;
set @xml =
'<Root>
<Cube>
<Cube time="2013-12-09">
<Cube currency="USD" rate="1.3722"/>
<Cube currency="EUR" rate="1.0"/>
</Cube>
<Cube time="2013-12-10">
<Cube currency="USD" rate="1.5555"/>
<Cube currency="EUR" rate="1.2"/>
</Cube>
</Cube>
</Root>'
SELECT FXDate.value('@time', 'varchar(100)') AS FXDATE
,FXRates.value('@currency','varchar(100)') AS FXCurrency
,FXRates.value('@rate','varchar(100)') AS FXRate
FROM @XML.nodes('Root/Cube/Cube') as Test(FXDate)
CROSS APPLY Test.FXDate.nodes('Cube') as Test2(FXRates);
I then realised it may have something to do with the namespaces(?) as I'd noticed someone else pointed this out for someone else' query. So now I am rather stuck on how to get the SQL to work with namespaces. As this file will be sent in as a parameter, I have to work with the name spaces.
This is my attempt at the SQL with the namespace except I get nothing in the results. Any feedback/answers would be much appreciated. Thanks!
declare @xml as xml;
set @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="2013-12-09">
<Cube currency="USD" rate="1.3722"/>
<Cube currency="EUR" rate="1.0"/>
</Cube>
<Cube time="2013-12-10">
<Cube currency="USD" rate="1.5555"/>
<Cube currency="EUR" rate="1.2"/>
</Cube>
</Cube>
</gesmes:Envelope>'
select @xml
;with xmlnamespaces('http://www.ecb.int/vocabulary/2002-08-01/eurofxref' AS ns
,'http://www.gesmes.org/xml/2002-08-01' as ns2 )
SELECT FXDate.value('@time', 'varchar(100)')
FROM @XML.nodes('ns:gesmes/Cube/Cube/Cube') as Test(FXDate)