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)
select @url
	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

DECLARE @xmlString VARCHAR(8000)
DECLARE @date datetime, @prevDate datetime
DECLARE @h int
DECLARE @flag bit
DECLARE @url varchar(255)

SELECT @flag = 0

CREATE table #temp ([Date] smalldatetime, Rate money, CurrencyId int)

SELECT @date = dateadd(day, 1, CAST(CONVERT(varchar(100), getdate(), 112) AS datetime))
SELECT @prevDate = @date

--получение курса доллара
--если курса нет берем курс на предыдущий день
WHILE @flag = 0
BEGIN
	SELECT @url = 'http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1='+
		Convert(char(10), @prevDate, 103) +'&date_req2='+
		Convert(char(10), @prevDate, 103)+'&VAL_NM_RQ=R01235'


	exec LoadXMLFromFile @url, @xmlString output
	--

	exec sp_xml_preparedocument  @h output, @xmlString

	INSERT INTO #temp ([Date], [Rate], CurrencyId)
	select 
		@date,--convert(smalldatetime, [Date], 104), 
		Convert(money, replace(Value, ',', '.')) 'Value',
		100
	from 
		OpenXML (@h, '//Record', 0)
	with 
	(
		[Date] char(10) '@Date',
		Nominal int './Nominal',
		Value varchar(10) './Value'
	)

	IF(@@ROWCOUNT > 0)
	BEGIN
		SET @flag = 1
	END
	ELSE
	BEGIN 
		SET @prevDate = dateadd(day, -1, @prevDate)
	END

	exec sp_xml_removedocument @h

END

select * from #temp
drop table #temp

ALTER  PROCEDURE [dbo].[LoadXMLFromFile]
(
	@tcFileName		VARCHAR(255),
	@tcXMLString	VARCHAR(8000) OUTPUT
) AS
BEGIN
	-- Scratch variables used in the script
	DECLARE @retVal INT
	DECLARE @oXML INT
	DECLARE @errorSource VARCHAR(8000)
	DECLARE @errorDescription VARCHAR(8000)
	DECLARE @loadRetVal INT

	-- Initialize the XML document
	EXEC @retVal = sp_OACreate 'MSXML2.DOMDocument', @oXML OUTPUT
	IF (@retVal <> 0)
	BEGIN
		-- Trap errors if any
		EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
		RAISERROR (@errorDescription, 16, 1)

		-- Release the reference to the COM object
		EXEC sp_OADestroy @oXML
		RETURN
	END

	EXEC @retVal = sp_OASetProperty @oXML, 'async', 0
	IF @retVal <> 0
	BEGIN
 		-- Trap errors if any
		EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
		RAISERROR (@errorDescription, 16, 1)

		-- Release the reference to the COM object
		EXEC sp_OADestroy @oXML
		RETURN
	END

	-- Load the XML into the document
	EXEC @retVal = sp_OAMethod @oXML, 'load', @loadRetVal OUTPUT, @tcFileName
	IF (@retVal <> 0)
	BEGIN
		-- Trap errors if any
		EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
		RAISERROR (@errorDescription, 16, 1)

		-- Release the reference to the COM object
		EXEC sp_OADestroy @oXML
		RETURN
	END

	-- Get the loaded XML
	EXEC @retVal = sp_OAMethod @oXML, 'xml', @tcXMLString OUTPUT
	IF (@retVal <> 0)
	BEGIN
		-- Trap errors if any
		EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
		RAISERROR (@errorDescription, 16, 1)

		-- Release the reference to the COM object
		EXEC sp_OADestroy @oXML
		RETURN
	END

	-- Release the reference to the COM object
	EXEC sp_OADestroy @oXML

END

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

Posted via ActualForum NNTP Server 1.4

DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type
SET @xmlDocument = N'
<?xml version="1.0" encoding="windows-1251" ?>
<ValCurs Date="04/04/2008" name="Foreign Currency Market">
<Valute ID="R01010">
	<NumCode>036</NumCode>
	<CharCode>AUD</CharCode>
	<Nominal>1</Nominal>
	<Name>Австралийский доллар</Name>
	<Value>21,5867</Value>
</Valute>
</ValCurs>
'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
select CharCode, NameVal, Nominal, Convert(money, replace(Value, ',', '.')) 'Value'
from OpenXML (@docHandle, '/ValCurs/Valute', 0)
with ( NameVal varchar(99) './Name', Nominal int './Nominal', Value varchar(10) './Value', CharCode varchar(9) './CharCode' )

exec sp_xml_removedocument @docHandle

Posted via ActualForum NNTP Server 1.4

DECLARE @xmlString VARCHAR(8000)
DECLARE @date datetime, @prevDate datetime
DECLARE @h int
DECLARE @flag bit
DECLARE @url varchar(255)

SELECT @flag = 0

CREATE table #temp ([Date] smalldatetime, Rate money, CurrencyId int)

SELECT @date = dateadd(day, 1, CAST(CONVERT(varchar(100), getdate(), 112) AS datetime))
SELECT @prevDate = @date


	SELECT @url = 'http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1='+
		Convert(char(10), @prevDate, 103) +'&date_req2='+
		Convert(char(10), @prevDate, 103)+'&VAL_NM_RQ=R01235'


	exec LoadXMLFromFile @url, @xmlString output
	--
    select @xmlString 

	EXEC @retVal = sp_OAMethod @oXML, 'xml', @tcXMLString OUTPUT
	IF (@retVal <> 0)
	BEGIN
		-- Trap errors if any
		EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
		RAISERROR (@errorDescription, 16, 1)

		-- Release the reference to the COM object
		EXEC sp_OADestroy @oXML
		RETURN
	END

Posted via ActualForum NNTP Server 1.4

Posted via ActualForum NNTP Server 1.4

-- часть от автора
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)
select @url
	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
-- часть от daw
select cast(floor(cast(@d1 as float)) as smalldatetime) as Data, CharCode, Nominal, Convert(money, replace(Value, ',', '.'))
'Value' 
from OpenXML (@h, '/ValCurs/Valute', 0) 
with ( Name varchar(99) './Name', Nominal int './Nominal', Value varchar(10) './Value', CharCode varchar(9) './CharCode' ) 

2008-04-04 00:00:00	AUD	1	21.5867
2008-04-04 00:00:00	GBP	1	46.9212
2008-04-04 00:00:00	BYR	1000	10.9960
2008-04-04 00:00:00	DKK	10	49.4883
2008-04-04 00:00:00	USD	1	23.6153
2008-04-04 00:00:00	EUR	1	36.9414
2008-04-04 00:00:00	ISK	100	31.4745
2008-04-04 00:00:00	KZT	100	19.6018
2008-04-04 00:00:00	CAD	1	23.2571
2008-04-04 00:00:00	CNY	10	33.6535
2008-04-04 00:00:00	NOK	10	45.9746
2008-04-04 00:00:00	XDR	1	38.5536
2008-04-04 00:00:00	SGD	1	17.0692
2008-04-04 00:00:00	TRY	1	18.3206
2008-04-04 00:00:00	UAH	10	46.8902
2008-04-04 00:00:00	SEK	10	39.4239
2008-04-04 00:00:00	CHF	1	23.2870
2008-04-04 00:00:00	JPY	100	22.9810

а файервол какой-нибудь ничего не запрещает вашему серверу?

Posted via ActualForum NNTP Server 1.4

это ни о чем не говорит. запрет или разрешение можно обычно и для конкретного
приложения поставить.