🙂 OK.
declare @x xml;
set @x = (SELECT * FROM OPENROWSET(BULK '<YOUR PATH ON SQL SERVER MACHINE>\rates.xml', SINGLE_CLOB) AS x);
with xmlnamespaces ('http://www.reuters.com/Rate' as rts)
select n1.a.value('fn:local-name(.)','varchar(20)') as rateType
,cast(left(n1.a.value ('./@Timestamp','varchar(20)'),8) as date )as rateDate
,cast(stuff(stuff(right(n1.a.value ('./@Timestamp','varchar(20)'),6),5,0,':'),3,0,':') as time(0)) as rateTime
,n2.a.value ('./@Name','varchar(20)') as rName
,n2.a.value ('./@Type','varchar(20)') as rType
,n2.a.value ('./@Buy','float') as rBuy
,n2.a.value ('./@Sell','float') as rSell
,n2.a.value ('./@Last','float') as rLast
from @x.nodes('rts:RateMessage/*') n1(a)
cross apply n1.a.nodes('Rate') n2(a) ;
Prerequisites. Sql server account must have access to the file.
File must have ANSI encoding.
Good luck.