Good GMT+1 evening.

I have a problem trying to twist one of the fine phil factor's articles to fit my needs.
This one is about using CURL to get exchange data rates.
I'm doing it from the BCE , that gives a zipped csv file with the current rates.
So , i made a simple bat file to get the file , unzip it and use the result.

@echo off
curl -S -s "http://www.ecb.int/stats/eurofxref/eurofxref.zip?id=816512" -o "eurofxref.zip"
unzip -p eurofxref.zip

so i call it bce.bat , and call it as

INSERT INTO #rawRSS(Contenido) EXECUTE master..xp_cmdshell @Command

now here creeps the problem. It should return 2 lines , one with the headers and one with the rates.

it returns 1 line of headers , and two lines of results

output                                                                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date, USD, JPY, BGN, CZK, DKK, EEK, GBP, HUF, LTL, LVL, PLN, RON, SEK, SKK, CHF, ISK, NOK, HRK, RUB, TRY, AUD, BRL, CAD, CNY, HKD, IDR, KRW, MXN, MYR, NZD, PHP, SGD, THB, ZAR,
4 June 2008, 1.5466, 161.82, 1.9558, 24.670, 7.4586, 15.6466, 0.79020, 242.70, 3.4528, 0.7006, 3.3763, 3.6276, 9.3448, 30.325, 1.6037, 119.48, 7.9755, 7.2497, 36.7500, 1.9200, 1.6100, 2.5271, 1.5610, 10.7319, 12.0754, 14407.35, 1575.99, 15.9609, 5.0179, 1
.9740, 67.996, 2.1081, 50.574, 12.0403,
NULL

(4 filas afectadas)

The problem here is that the result is chomped off at 255  , thus varchar(255) is. Like stated in the documentation , results are nvarchar(255).

Is there any way to make the server bend the knee and use any other length ?

I think , i could concat the results but it reeks of bad style.

(btw , great articles Mr Phil Factor )

--
edit : my english is not so good , i phrased the problem mistakenly

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall

Ok , I give up on csv. A quick and dirty approach with XML works just fine.
Here is the code. I suppose it could be done more elegantly , but at least it works

DECLARE @Command VARCHAR(8000)
declare @i int
declare @j int
declare @xmldoc varchar(4000)
declare @linea varchar(8000)
SELECT @Command= 'c:\curl\curl -s -S "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"'
CREATE TABLE #rawRSS (Linea INT IDENTITY(1,1),Contenido NVARCHAR(4000))
INSERT INTO #rawRSS(Contenido) EXECUTE master..xp_cmdshell @Command
set @i = @@rowcount
set @j=1
set @xmldoc = ''
while @i > 0
begin
select  @linea= Contenido from  #rawRSS where Linea = @j
if @linea like '%<Cube%' or @linea like '%</Cube%'
begin
select  @xmldoc = @xmldoc + REPLACE(@linea,'''','"')
end
set @i = @i-1
set @j = @j+1
end
declare @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc
SELECT    currency as Codigo, rate as cambio
FROM       OPENXML (@idoc, '/Cube/Cube/Cube',1)
            WITH ( [currency]  varchar(4),[rate] decimal(12,4))

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall

Ah, sorry, I didn't spot that this had been unanswered.
The old trick xp_cmdShell 'type filename' has the fundemental problem that the shell that is called is only 255 characters wide. This is, I suspect, because in the days that this routine was devised, the longest varchar was 255 chars long. It is from this dark era that the folk wisdom that SQL Server was useless for string manipulation originated.
In SQL Server 2005, you can pull any file into a variable, using BCP extensions, and then chop it up into lines.
The rest of the routine should be fine for your needs.

Thanks for the tip. I already got confortable with the xml option , it gives me other benefits on the long road , albeit the code is awfull , but if it works , i'm a little remiss to touch it ;)

---

Hey , sysadm , leave the users alone
They are nothing more
than another brick in the firewall