declare @xmlString nvarchar(4000), @url varchar(255), @retVal INT, @oXML INT, @loadRetVal INT, @h int
declare @d1 datetime
set @d1 = GetDate()

select @url = 'http://www.cbr.ru/scripts/XML_daily.asp?date_req=' + Convert(char(10), @d1, 103)
EXEC @retVal = sp_OACreate 'MSXML2.DOMDocument', @oXML OUTPUT
EXEC @retVal = sp_OASetProperty @oXML, 'async', 0
EXEC @retVal = sp_OAMethod @oXML, 'load', @loadRetVal OUTPUT, @url
EXEC @retVal = sp_OAMethod @oXML, 'xml', @xmlString OUTPUT
EXEC sp_OADestroy @oXML

exec sp_xml_preparedocument  @h output, @xmlString
select cast(floor(cast(@d1 as float)) as smalldatetime) as Data, CharCode, Nominal, Convert(money, replace(Value, ',', '.')) 'Value'
from OpenXML (@h, '//Valute', 0)
with ( Name varchar(99) './Name', Nominal int './Nominal', Value varchar(10) './Value', CharCode varchar(9) './CharCode' )

exec sp_xml_removedocument @h